Excel conditional formatting to add multiple words to SEARCH

Question

I'm currently using this code to add some conditional formatting to column A:

Range("A2:A5000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=AND(SEARCH(""CTC"",$S2),$I2>=10000,$N2>=30)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 255
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Unfortunately I just found out that sometimes instead of "CTC" I need to search for "critical to close", or "critical tc", etc. Is there a way I can easily modify that SEARCH function to include more criteria, perhaps by creating an array?

My fear is completely overhauling how I write it because I use that SEARCH function in many other places as well. I'd like to be able to do a simple find and replace in my code to prevent having to make many, many changes...

Thanks for the help-

You can add similar search function with or operator for the other values you want to search — Sivaprasath Vadivel, Mar 22 '17 at 16:06
Try this `"=AND(SUMPRODUCT(--(ISNUMBER(SEARCH({""CTC"",""critical tc"",""critical to close""},$S2))))>0,$I2>=10000,$N2>=30)"` — Scott Craner, Mar 22 '17 at 16:07
http://stackoverflow.com/questions/14803944/excel-searching-for-multiple-terms-in-a-cell check out this link — Sivaprasath Vadivel, Mar 22 '17 at 16:09

Excel conditional formatting to add multiple words to SEARCH

0 Answers0