VBA & Macro Problems

How to Make Matching Words Bold and Red in Excel Using VBA

Maira Mehtab Sep 21, 2026 870 views

Question details

The user wants to find specific text strings across multiple cells in a spreadsheet and automatically apply bold and red font formatting to those matching words.

Product: Excel
Device & OS: not provided
Scenario: Formatting specific target words within larger blocks of text across a selected range of cells without manually editing each cell.
Observed behavior: A VBA macro script is needed to loop through selected cells, locate the exact character positions of the target word, and apply the required font modifications.

Before you start

Ensure that you have enabled Developer options and macro execution in your spreadsheet software settings before attempting to run the VBA code.

Solution 1Recommended

Run a VBA Script to Format Matching Characters

Use a custom VBA macro to search within your active selection, locate every occurrence of your target word, and format only those characters as bold and red.

This method uses the VBA 'Find' function to locate cells containing your target word, and then uses the 'InStr' function to identify the exact starting position of the word within the cell's text string. It then applies the bold attribute and a color index of 3 (red) specifically to those characters.

Open the VBA Editor

Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

In the top menu, click on 'Insert' and then select 'Module' to create a blank script window.

Paste the Macro Code

Copy and paste the following code into the module window: ```vba Sub BoldWord() Const wrd = "potato" Dim rng As Range Dim adr As String Dim txt As String Dim pos As Long With Selection Set rng = .Find(What:=wrd, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False) If Not rng Is Nothing Then adr = rng.Address Do txt = rng.Value pos = 0 Do pos = InStr(pos + 1, txt, wrd, vbTextCompare) If pos = 0 Then Exit Do With rng.Characters(Start:=pos, Length:=Len(wrd)).Font .Bold = True .ColorIndex = 3 End With Loop Set rng = .FindNext(After:=rng) If rng Is Nothing Then Exit Do Loop Until rng.Address = adr End If End With End Sub ```

Select the Target Range

Close the VBA editor and go back to your worksheet. Highlight the specific range of cells where you want to search and format the text.

Run the Macro

Press 'Alt + F8', select 'BoldWord' from the Macro dialog box, and click 'Run'.

Customize the Target Word: To search for a different word, simply change the 'Const wrd = "potato"' line in the script to match your desired text (e.g., Const wrd = "urgent").

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office provides robust built-in support for VBA macros in its advanced versions. You can execute the exact same text-formatting scripts you use in Excel without needing to modify your code.

1. Open your Spreadsheet: Launch WPS Office and open the workbook where you want to format the text.
2. Access the Developer Tab: Navigate to the 'Developer' tab on the top ribbon. If it is hidden, you can enable it in the WPS Options menu.
3. Open the VBA Editor: Click the 'VBA Editor' button to open the macro programming environment.
4. Execute the Code: Insert a module, paste your text-formatting code, and run it directly on your selected cells.

High compatibility with Microsoft Excel (.xls, .xlsx, .xlsm) formatsSeamless execution of existing Excel VBA scripts and userformsLightweight application with fast launch and processing speedsFamiliar tabbed interface that makes developer tools easy to find

microsoft office alternative - wps office

Frequently Asked Questions

Can I change the font color to something other than red?

Yes, you can easily modify the script by changing the '.ColorIndex = 3' property. For instance, setting it to 5 will change the targeted text color to blue, and setting it to 10 will change it to green.

Will running this macro overwrite the existing formatting of the entire cell?

No, the script uses 'rng.Characters' to specifically target the exact character positions of the matched word. The rest of the text in the cell, as well as the cell's background color and general formatting, will remain completely unaffected.

Does this VBA script distinguish between uppercase and lowercase letters?

In the provided script, case sensitivity is turned off. The settings 'MatchCase:=False' and 'vbTextCompare' ensure that the macro will find and format both uppercase and lowercase variations of your target word.

Why is the macro not highlighting the word in some of my cells?

The script is designed to run exclusively on currently highlighted cells due to the 'With Selection' parameter. You must ensure you have highlighted the specific cells or column you want to process before running the macro.

Excel & Data Problems