CODEWITHSUNDEEP
vbaexcelexcel-formulaconditional-formatting
TKE-439
TKE-439

Reputation: 87

Multiple Conditional Formatting Variables in VBA

Here's what I've got at the moment:

'Highlight If N=19 & R=OR
Range("G4:R1000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$N4=19"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 255
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True

I am trying to highlight a few cells based on multiple criteria. If N=19 and if R=OR. I can only get the N=19 portion to work.

Upvotes: 0

Views: 646

Answers (1)

user4039065
user4039065

Reputation:

I believe the formula adjustment I made in comments above should solve your problem but here is how I clean up recorded Conditional Formatting code.

With Worksheets("Sheet1")
    With .Range("G4:R1000")
        With .FormatConditions.Add(Type:=xlExpression, Formula1:="=AND($N4=19, $R4=""OR"")")
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 255
                .TintAndShade = 0
            End With
            .SetFirstPriority
        End With
        .FormatConditions(1).StopIfTrue = True
    End With
End With

Removing the verbose qualifying code (e.g. Selection.FormatConditions(Selection.FormatConditions.Count)...) makes it much more readable.

Upvotes: 1

Related Questions