CODEWITHSUNDEEP
vbaexcelconditional-formattingmultiple-conditions
thethethe
thethethe

Reputation: 31

FormatConditions for multiple conditions

I want to format a range based on multiple conditions.

For example, highlight cells from columns(a:g) if their value is less than 0 AND if the value in their respective row in column H contains the string "yes" or "no".

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

Upvotes: 2

Views: 1633

Answers (2)

Alena
Alena

Reputation: 1

there is something wrong with the formula, at least " is in wrong position, I cannot get it to work Expect Instruction End I found something else which works easier for AND conditions by multiplying conditions and compare to 1

ex: Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=((F3<$B3) * ($B3<51) * (F3<> 0))=1"

Upvotes: 0

user4039065
user4039065

Reputation:

Try it as,

With Worksheets("sheet2").Columns("A:G")
    .FormatConditions.Delete
    With .FormatConditions.Add(Type:=xlExpression, _
      Formula1:="=AND(OR($H1=""yes"", $H1=""no""), $G1<0, $A1<0)")
        .Interior.Color = 120000
        .StopIfTrue = False
    End With
End With

After rereading your original narrative, I believe that this may be a better formula.

Formula1:="=AND(OR($H1=""yes"", $H1=""no""), A1<0")

Upvotes: 1

Related Questions