Macro to Highlight Cells containing a Specific Text

These VBA macros find a word or phrase in the selected cells and highlight it: either the whole cell, or just the matching text inside the cell, in red and bold.

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Highlight the whole cell

Select the cells, run the macro and type the text to find:

Sub HighlightCellsWithText()
    Dim t As String, c As Range
    t = InputBox("Highlight cells containing:")
    If t = "" Then Exit Sub
    For Each c In Selection
        If InStr(1, c.Text, t, vbTextCompare) > 0 Then
            c.Interior.Color = RGB(255, 235, 156)
        End If
    Next c
End Sub

vbTextCompare ignores case, so kochi matches Kochi. Use vbBinaryCompare for an exact-case match.

Highlight just the matching text

Colours every occurrence of the text inside each cell:

Sub HighlightMatchingText()
    Dim t As String, c As Range, p As Long
    t = InputBox("Text to highlight:")
    If t = "" Then Exit Sub
    For Each c In Selection
        If Not c.HasFormula Then
            p = InStr(1, c.Value, t, vbTextCompare)
            Do While p > 0
                With c.Characters(p, Len(t)).Font
                    .Color = vbRed
                    .Bold = True
                End With
                p = InStr(p + Len(t), c.Value, t, vbTextCompare)
            Loop
        End If
    Next c
End Sub

This works on typed text only, because Excel can’t format part of a formula’s result.

Without a macro: conditional formatting

1. Select the cells.
2. Go to Home → Conditional Formatting → Highlight Cells Rules → Text that Contains.
3. Type the text and pick a format.

To count or filter the matches instead, see IF cell contains text and COUNTIF with wildcards: =COUNTIF(A2:A100,"*kochi*").

Things to know

• Highlighting can’t be undone, so try it on a copy first.
• To remove the highlighting, select the cells and use Home → Clear → Clear Formats.

Related macros