Hi,

I am looking to highlight every instance of a word (or series of words) in an Excel spreadsheet. I've searched through the forums and found a lot of excellent information regarding my issue but I can't quite get it to work.

The below code works to a point. 

Sub HighlightCells()
    Dim Lookin As Range, ff As String
    Dim i As Long
    Dim Fnd As Variant
    Dim fCell As Range
    Dim ws As Worksheet
    Dim xitem As Variant
    
  Fnd = Array("select ", "update ", "insert ")


    For Each ws In Worksheets
        With ws
            For Each xitem In Fnd
                Set Lookin = .Cells.Find(xitem, Lookin:=xlValues, LookAt:=xlPart)
                If Not Lookin Is Nothing Then
                    ff = Lookin.Address
                    Do
                       Lookin.Characters(InStr(1, Lookin, xitem), Len(xitem)).Font.ColorIndex = 3
                        Set Lookin = .Cells.FindNext(Lookin)
                    Loop Until ff = Lookin.Address
                End If
                Set Lookin = Nothing
            Next
        End With
    Next

End Sub
It searches for instances of the words in the array called Fnd and highlights them in another color. These words are 'select ', 'update ' and 'insert '. My issue with this code is that it only seems to highlight the first instance of each word in each cell. I need it to highlight all intances of the word and in some cases there will be more than one instance of each word per cell.

Any help would be greatly appreciated.