VBA & Macro Problems

How to Change the Font Color of Specific Words Inside Excel Cells

Maira Mehtab Sep 22, 2026 870 views

Question details

The user needs to change the font color of a specific word located within larger text strings across more than 1,000 Excel cells.

Product: Excel
Device & OS: Windows or Mac (Desktop Versions)
Scenario: Formatting specific words automatically in a large dataset containing long text entries without manually editing every single cell.
Observed behavior: Excel's native Find and Replace tool applies the new font formatting to the entire contents of the cell instead of isolating and formatting only the matched word.

Before you start

Make sure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and back up your file, as VBA macro executions cannot be undone with the standard Undo button.

Solution 1Recommended

Use a VBA Macro to Format Specific Words in Cells

Executing a VBA macro is the only automated way to isolate partial text strings within cells and apply formatting specifically to those characters.

Because Excel's built-in formatting tools apply changes to the whole cell when used in bulk operations like Find and Replace, VBA is necessary for targeting partial text. The macro iterates through a selection, calculates the starting character position and string length of the target word, and uses the Characters property to apply a specified font color.

Open the Visual Basic Editor

Press Alt + F11 on a Windows PC (or Option + F11 on a Mac) to launch the Visual Basic for Applications (VBA) editor.

Insert a Standard Module

Click on 'Insert' in the top menu and select 'Module' to open a blank coding window.

Write the Macro Code

Create a macro that loops through each cell in the selection, uses the InStr function to find the word, and applies Cell.Characters(StartPos, Length).Font.Color = vbRed.

Run the Macro on Your Data

Close the VBA editor, select the range of cells containing the text you want to format, go to the 'Developer' tab, click 'Macros', select your new macro, and click 'Run'.

Static Text Requirement: The VBA Characters property only works on cells containing plain, static text. It cannot format parts of a text string that is generated by an active formula.

Advanced Spreadsheet Tool

Run Formatting Macros Seamlessly with WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for VBA macros, making it incredibly easy to automate complex tasks like formatting specific words inside cells. It offers an intuitive interface tailored for efficiency and high productivity.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx or .xlsm file containing the data.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click 'Visual Basic' to open the code editor.
3. Execute your formatting script: Insert a module, paste your word-coloring VBA script, highlight your target cells in the worksheet, and run the macro.

Full compatibility with Microsoft Excel VBA macro files (.xlsm).Seamless support for standard Office formats including .xlsx, .xls, and .csv.Built-in Visual Basic editor to create, edit, and run scripts instantly.Lightweight software architecture that ensures fast processing of large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use Find and Replace to color just one word in a cell?

No, Excel's native Find and Replace tool will apply the selected formatting to the entire contents of the cell, not just the word you searched for. Automating partial formatting requires VBA.

How do I change the macro to use a different font color?

In your VBA code, locate the constant 'vbRed'. You can change this to another standard VBA color like 'vbBlue' or 'vbGreen', or use the RGB function, such as RGB(255, 165, 0), for a custom color.

Why doesn't my VBA macro work in Excel Online?

Excel for the Web does not support VBA macros. You must open your workbook in a desktop version of Excel or WPS Spreadsheet on Windows or Mac to execute VBA code.

Will this macro work if my cell text is generated by a formula?

No, partial cell formatting is impossible on cells driven by formulas. You must copy the formula cells and paste them as 'Values' before running the formatting macro.

Excel & Data Problems