Excel VBA FormatCondiitons for multiple conditions

Question

I'm a novice in vba and I was confused on how to format a range based on multiple conditions. For example, I want to highlight the cells from columns(a:g) only if their value is less than 0 AND if the value in their respective row in column H contained the string "yes" or "no". The code I've written to perform this function doesn't work but looks like this.

Columns("A:G").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(OR($H1 = ""YES"", $H1 = ""NO""),Columns(A,G) < 0)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 120000
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Could anyone point me to the right direction? Thanks!

Excel VBA FormatCondiitons for multiple conditions

1 Answers