Highlight Specific Words Inside Excel Cells with VBA
Question details
The user needs to highlight or change the font formatting of specific words within Excel cells without formatting the entire cell.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- The worksheet contains single-line or multiline cell text, and the same search phrase may appear more than once in a cell.
- Observed behavior
- Conditional formatting applies formatting to the full cell and cannot change the color or style of only selected words within its text.
Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) before adding or running VBA code. Select the cells to process and confirm the exact word or phrase you want the macro to find.
Use a VBA macro to format every matching phrase in selected cells
Use this method when you need to change the font color, bold style, or other formatting for only the matched text inside each selected Excel cell.
The Excel Characters object formats a character range within a cell instead of formatting the cell as a whole. The macro below searches each selected cell repeatedly, so it can format multiple occurrences of the same phrase, including text in cells with line breaks.
In Excel, press Alt+F11, then choose Insert > Module to create a standard VBA module.
Paste this code into the module: Sub HighlightMatchingText() Dim cell As Range Dim searchText As String Dim startPos As Long Dim foundPos As Long searchText = InputBox("Enter the word or phrase to highlight:") If Len(searchText) = 0 Then Exit Sub For Each cell In Selection If Not IsError(cell.Value) Then startPos = 1 Do foundPos = InStr(startPos, CStr(cell.Value), searchText, vbTextCompare) If foundPos = 0 Then Exit Do With cell.Characters(foundPos, Len(searchText)).Font .Color = RGB(255, 0, 0) .Bold = True End With startPos = foundPos + Len(searchText) Loop End If Next cell End Sub
Return to the worksheet, select the cells that contain the text, press Alt+F8, select HighlightMatchingText, and click Run. Enter the search phrase when prompted.
Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm) so the VBA module remains available when you reopen the file.
Adjust the macro for case-sensitive matching or a different text style
Use this option when exact capitalization matters or when you want a highlight color, underline, or another font effect instead of red bold text.
Highlight Specific Text Inside Cells with WPS Spreadsheet Macros
WPS Spreadsheet can help you format selected words within a cell through macro tools and character-level text formatting. Create a macro-enabled workbook, add compatible VBA-style macro code, and run it on the selected range to style each matching phrase.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, open the workbook in WPS Spreadsheet, and select the cells containing the text to format.
- 2. Enable the macro tools: Open the Developer or Macro tools area in WPS Spreadsheet and choose the Visual Basic editor or macro editor available in your installation.
- 3. Insert a standard macro module: Create a new module and paste the VBA-style macro that uses the Characters method to locate and format the matching text.
- 4. Run the macro on the selected range: Return to the worksheet, open Macro > View Macros, select the formatting macro, and run it after entering the required search phrase.
- 5. Save as a macro-enabled file: Use Save As and choose the .xlsm format to retain the workbook's macro content and Excel compatibility.
Frequently Asked Questions
Can conditional formatting highlight only one word in an Excel cell?
No. Excel conditional formatting applies its result to the entire cell. Use VBA and Range.Characters(Start, Length).Font to format only the matching characters.
Does the VBA macro work when the same word appears multiple times in one cell?
Yes. A loop that advances the starting position after each InStr match can find and format every occurrence in the cell.
Will the macro work with line breaks inside a cell?
Yes. A line break is part of the cell text, and InStr can search text across multiline cell content as long as the search phrase matches the stored characters.
How can I make the word search case-sensitive in VBA?
Use vbBinaryCompare as the fourth argument of InStr. Use vbTextCompare when uppercase and lowercase letters should be treated as the same.
Why must I save the workbook as XLSM?
The .xlsx format does not retain VBA projects. Save the workbook as Excel Macro-Enabled Workbook (*.xlsm) to preserve and run the macro later.
