VBA & Macro Problems

Highlight Specific Text Within Excel Cells Using VBA

Steve KSteve K Nov 18, 2026 869 views

Question details

Highlight every occurrence of specified text within selected Excel cells without formatting the entire cell.

Highlight Specific Text Within Excel Cells Using VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the Developer tab and select Visual Basic. If the Developer tab is hidden, enable it in Excel's ribbon settings.

2
Insert the macro

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

3
Select cells and run the macro

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.

4
Save the workbook

Choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm) to keep the macro with the workbook.

Use a VBA Macro to Color Matching Text in Selected Cells
Formatting and undo: The macro changes font color rather than cell fill. Running a macro generally clears Excel's undo history, so test it on a small range first. Change RGB(255, 0, 0) in the code to use another font color.
Free Microsoft Office alternative

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.

Use familiar spreadsheet tools to find and format text in cellsOpen and edit common Microsoft Excel formats, including XLSXMove between familiar Office-style tabs and commandsWork with a free, lightweight office suite
microsoft office alternative - wps office

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.