How to highlight every instance of a word in the s

Question

I would like to highlight in red and bold every instance of a word/phrase in the selected column of my Excel sheet (using Excel 2010). For example, if columns A1:A10 contain the sentence, "The brown fox likes the other brown fox," I would like to highlight every instance of "brown fox" in this range.

I found a macro here which highlights only the first instance of "brown fox" in every cell:

Sub colorText()

Dim cl As Range
Dim startPos As Integer
Dim totalLen As Integer
Dim searchText As String

' specify text to searh.
searchText = "brown fox"

' loop trough all cells in selection/range
For Each cl In Selection

  totalLen = Len(searchText)
  startPos = InStr(cl, searchText)

  If startPos > 0 Then
    With cl.Characters(startPos, totalLen).Font
      .FontStyle = "Bold"
      .ColorIndex = 3
    End With
  End If
Next cl

End Sub

I'd like to edit this macro so that it highlights every instance of "brown fox," not just the first. As an attempt, I tried the following:

Sub colorText()

Dim cl As Range
Dim startPos As Integer
Dim totalLen As Integer
Dim searchText As String
Dim endPos As Integer
Dim testPos As Integer

' specify text to search.
searchText = "brown fox"

' loop trough all cells in selection/range
For Each cl In Selection

  totalLen = Len(searchText)
  startPos = InStr(cl, searchText)
  testPos = 0

  Do While startPos > testPos
    With cl.Characters(startPos, totalLen).Font
      .FontStyle = "Bold"
      .ColorIndex = 3
    End With

    endPos = startPos + totalLen
    testPos = testPos + endPos
    startPos = InStr(testPos, searchText)
  Loop

Next cl

End Sub

However, this still only formats the first instance of "brown fox."

Any thoughts/edits would be much appreciated.

Answer 1

Your error is on your logic. You should correct the code as below:

 startPos = InStr(testPos, cl, searchText, vbTextCompare)

Instead of doing this one:

 startPos = InStr(testPos, searchText)

In the second sub. Do you see now? :-)

Answer 2

I had the same problem when I was looking to format Specific words in a range of Cells. After several attempts and a lot of Internet searches, this is the one that worked the best...

Sub FormatWords()
Dim Rng As Range, cl As Range, Red As Integer
Dim oStrg As String
Set Rng = Range(Range("D1"), Range("D" & Rows.Count).End(xlUp))
On Error Resume Next
oStrg = "Apple"
If oStrg = "" Then Exit Sub

For Each cl In Rng
    Red = InStr(1, cl, oStrg, vbTextCompare)

    Do Until Red = 0
        With cl.Characters(Red, Len(oStrg))
        .Font.Color = RGB(230, 25, 55)
        .Font.Bold = True
         End With
         Red = InStr(Red + 1, cl, oStrg, vbTextCompare)
    Loop
Next cl

oStrg = "Mango"
If oStrg = "" Then Exit Sub

For Each cl In Rng
    Orange = InStr(1, cl, oStrg, vbTextCompare)
    Do Until Orange = 0
        With cl.Characters(Orange, Len(oStrg))
        .Font.Color = RGB(250, 200, 0)
        .Font.Bold = True
        End With

        Orange = InStr(Orange + 1, cl, oStrg, vbTextCompare)
    Loop
Next cl

End Sub