Conditional Formatting in VBA based on A Different Cell

Question

I am writing a macro as a part of a larger project. The last bit is to apply conditional formatting to a cell in column V if the conditions are met in column W.

I get

Runtime Error 5: Invalid procedure call or argument error.

Sub ComConFormGreen()
'
' ComConForGreen Macro
'

'
    Columns("W:W").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""Each"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Columns("V:V").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""$W1=""Each"""""
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

The problem appears to be in the following line:

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
            Formula1:="=""$W1=""Each"""""

Not related to the question, but what happens if you run the code with the wrong sheet accidently selected? [how-to-avoid-using-select-in-excel-vba](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Darren Bartrup-Cook, Sep 10 '21 at 08:13

score 1 · Answer 1 · answered Sep 09 '21 at 17:50

1

Your conditional rule looks for the value in xlCellValue, but you are trying to resolve a formula, so you should use xlExpression instead.

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$W1=""Each"""

answered Sep 09 '21 at 17:50

Elio Fernandes

1,335
2
14
29

That was it! Thanks! – Icoeph Sep 09 '21 at 17:54
@Icoeph, check the answer if it was helpful! – Elio Fernandes Sep 09 '21 at 17:56

Conditional Formatting in VBA based on A Different Cell

1 Answers1