Highlight Specific Text Within Excel Cells Using VBA
Question details
Highlight every occurrence of specified text within selected Excel cells without formatting the entire cell.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to search a selected range and apply font formatting only to matching characters.
- Observed behavior
- Excel Find selects matching cells but does not highlight only the matching characters within them.
Open the workbook in a version of Excel that supports VBA, and make sure the target worksheet allows formatting. The macro below processes static text cells, skips formulas, and changes matching text to red.
Use a VBA Macro to Color Matching Text in Selected Cells
Run this macro when you want to format all non-overlapping occurrences of a search term in the selected text cells.
The macro uses InStr to find matches and the Characters property to change only their font color. Matching is case-insensitive, and existing formatting on other characters is left unchanged.
In Excel, open the Developer tab and select Visual Basic. If the Developer tab is hidden, enable it in Excel's ribbon settings.
In the VBA editor, select Insert > Module and paste this code: Sub HighlightMatchingText() Dim cell As Range Dim searchText As String Dim cellText As String Dim matchPos As Long If TypeName(Selection) <> "Range" Then MsgBox "Select a range of cells first." Exit Sub End If searchText = InputBox("Enter the text to highlight:", "Highlight Text") If Len(searchText) = 0 Then Exit Sub For Each cell In Selection.Cells If Not cell.HasFormula Then If VarType(cell.Value2) = vbString Then cellText = CStr(cell.Value2) matchPos = InStr(1, cellText, searchText, vbTextCompare) Do While matchPos > 0 cell.Characters(matchPos, Len(searchText)).Font.Color = RGB(255, 0, 0) matchPos = InStr(matchPos + Len(searchText), cellText, searchText, vbTextCompare) Loop End If End If Next cell End Sub
Return to the worksheet and select the target cells. Choose Developer > Macros, select HighlightMatchingText, and click Run. Enter the text to highlight and click OK.
Choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm) to keep the macro with the workbook.

Edit Excel Workbooks with WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move your workbooks. The Excel VBA macro described above is not a guaranteed WPS solution; macro availability and compatibility can vary.

Frequently Asked Questions
Can I make the macro match uppercase and lowercase exactly?
Yes. In the VBA editor, replace vbTextCompare with vbBinaryCompare in both InStr calls, then run the macro again.
Why are matches in formula cells skipped?
The macro checks cell.HasFormula and skips those cells because partial character formatting is intended here for static text. Copy formula results as values first if you need to format them as text.
How do I choose a different highlight color?
Open Developer > Visual Basic and change RGB(255, 0, 0) in the macro to the RGB values for your preferred font color.
Why do I need to save the workbook as XLSM?
The XLSM format preserves VBA macros in the workbook. Use File > Save As and choose Excel Macro-Enabled Workbook; an XLSX file cannot retain the macro.




