How to highlight every instance of a word in the same cell for a given range or selection in Excel 2010?

excel, format, vba

Solution

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? :-)

Problem

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.

Original source