I have the following code but it is only finding the final search term because it overwrites the previous. How would I would I write a second loop so that once all of the first values are highlighted it moves to the second Fnd value, and then make it salable to a long list of search terms?
Sub HighlightCells2()
Dim i As Long
Dim Fnd As String
Dim fCell As Range
Dim ws As Worksheet
'Values I want to find
Fnd = "*Boston*"
Fnd = "*OBI*"
For Each ws In Worksheets
With ws
Set fCell = .Range("A1")
For i = 1 To WorksheetFunction.CountIf(.Cells, Fnd)
Set fCell = .Cells.find(What:=Fnd, After:=fCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If fCell Is Nothing Then
MsgBox Fnd & " not on sheet !!"
Exit For
Else
With fCell
.Interior.ColorIndex = 6
End With
End If
Next i
End With
Next ws
End Sub
Bookmarks