Excel VBA Highlight Cells in Range that Do Not Contain Text from a List of Words

0
votes

I am trying to search column B and highlight all cells that contain text other than the words Flyer, Bulk Clearance, Eat In Season, Line Drive, Market Special, Push Item, and Weekender. I have it starting in the third row on purpose.

The code below works for just one word (Flyer), but I'd like the macro to be able to compare the text in the cell to the list of words mentioned above and highlight if the text differs from what is in the list.

Sub Orange()
Dim LR As Long, i As Long

LR = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
For i = 3 To LR
    With Range("B" & i)
        If .Value <> "Flyer" Then .Cells.Interior.ColorIndex = 45
    End With
Next i

MsgBox "The orange are not valid values."
End Sub
3
vbaexcel
Related (really a duplicate) and asked earlier today. Take a look: stackoverflow.com/questions/30755945/… – Byron Wall
Somehow you need to make a list of entries to search through. The Split based on a string of entries separate by | or something is a good approach. You can then iterate those with a For Each... – Byron Wall
You don't really need VBA -- you could do this with conditional formatting. Put your list of words in a contiguous range, and then use a CF Formula This would have the advantage of being dynamic, and avoiding the necessity to clear the colors when contents change to something valid. – Ron Rosenfeld
A better CF formula might be something like =ISNUMBER(MATCH($B3, <range with word list>, 0)) or =COUNTIF(<range with word list>, $B3). – user4039065

3 Answers

1
votes

One easy way to do this is to create a Dictionary storing the valid values and then checking to see if they're in the Dictionary:

'Requires reference to Microsoft Scripting Runtime.
Sub Orange()
    Dim temp() As String

    temp = Split("Flyer,Bulk Clearance,Eat In Season,Line Drive,Market Special,Push Item,Weekender", ",")

    Dim items As New Dictionary, item As Variant
    For Each item In temp
        items.Add item, item
    Next item

    Dim LR As Long, i As Long

    LR = ActiveSheet.Range("B" & Rows.Count).End(xlUp).row
    For i = 3 To LR
        With Range("B" & i)
            If Not items.Exists(.Value) Then .Cells.Interior.ColorIndex = 45
        End With
    Next i

    MsgBox "The orange are not valid values."
End Sub
0
votes

Perhaps this??

Sub HighlightFound()
Dim FoundCell As Range, MyArr As Variant, X As Long, FindRange As Range
MyArr = Array("Flyer", "Bulk Clearance", "Eat In Season", "Line Drive", "Market Special", "Push Item", "Weekender")
Set FindRange = Range("B3:B" & Range("B" & Rows.Count).End(xlUp).Row)
For X = LBound(MyArr) To UBound(MyArr)
    Set FoundCell = FindRange.Find(MyArr(X))
    Do Until FoundCell Is Nothing
        If FoundCell.Row > FindRange.FindNext(after:=FoundCell).Row Then
            Exit Do
        Else
            FoundCell.Interior.ColorIndex = 45
            Set FoundCell = FindRange.FindNext(after:=FoundCell)
        End If
    Loop
Next
End Sub
0
votes

Just use conditional formatting rules. Home > Conditional formatting > Highlight cell rules > Text that contains

And then just have a rule for each word where you either: - manually type the word - or use a cell reference and point it to a cell that it should look at