Conditional formatting with multiple conditions and checkboxes

Question

I'm trying the following conditional formatting to highlight cell A based on the value of cells B, C, D but it raises the error type mismatch.

    Sub logic()

    Dim i As Long
    For i = 1 To 3

    Range(Replace("A#", "#", i)).Select
    Range("A" & i).Activate

    Application.CutCopyMode = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$B$" & i And "=$C$" & i Or "=$D$" & i

    With Selection.FormatConditions(1).Interior
        .Color = RGB(100, 100, 100)
    End With

    Selection.FormatConditions(1).StopIfTrue = False

    Next i

End Sub

asylumax asylumax · Accepted Answer · 2020-06-07T16:36:18

See:

Conditional formatting using 3 conditions in Macro/VBA

Also, I ran a similar macro, and one thing that was interesting was that if you ran it multiple times, it returned an error. So, I added:

Selection.FormatConditions.Delete

before the formatting command, so you clear anything previously set.

Conditional formatting with multiple conditions and checkboxes

1 Answers