CODEWITHSUNDEEP
excelvba
Icoeph
Icoeph

Reputation: 11

Conditional Formatting in VBA based on A Different Cell

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"""""

Upvotes: 1

Views: 240

Answers (1)

Elio Fernandes
Elio Fernandes

Reputation: 1420

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"""

Upvotes: 1

Related Questions