Search code examples
excelvbaconditional-formatting

How to add multiple DoesNotContain text strings to Conditional Formatting VBA

I am looking to add multiple DoesNotContain text strings to this VBA. It may be easier to use a formula here when setting up the conditional formatting, but I am curious if it is possible or easy to add onto this VBA. Thanks in advance!

Selection.FormatConditions.Add Type:=xlTextString, String:="day", _ 
    TextOperator:=xlDoesNotContain
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .color = 12632256
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("A3:A").Select
    ActiveWindow.SmallScroll Down:=0
Solution

  • From the link posted in the comments above...

    Put your list of terms in a column and name the range of cells as (eg) "MyTerms"

    Add a CF rule which is formula-based using (eg)

    =SUM(COUNTIF(A1,"*" & MyTerms & "*"))=0   'formatting A1:A10

    That should highlight any cells which don't contain any of the terms in your list.