VBA & Macro Problems

How to Change Only Red Characters to Blue in Excel

Partner Editor Sep 27, 2026 868 views

Question details

The user needs to change the font color of specific red characters to blue within cells that contain a mix of red and black text.

How to Change Only Red Characters to Blue in Excel

Product: Excel
Device & OS: not provided
Scenario: A worksheet has hundreds of cells containing mixed-color text. The user wants to automate replacing only the red text with blue text.
Observed behavior: Changing the font color for the entire cell using standard formatting tools changes all text in the cell, including the black characters, instead of targeting only the red ones.

Before you start

Make sure to save a backup copy of your workbook before running the macro, as VBA changes to cell formatting cannot be easily undone.

Solution 1Recommended

Use a VBA Macro to Scan and Change Red Characters

This solution uses a custom VBA script that iterates through text constants character by character, replacing red fonts with blue fonts while leaving other colors intact.

Because standard Excel formatting tools apply color changes to the entire cell at once, VBA is required to evaluate and modify specific characters within a cell's text string.

Open the VBA Editor

Press 'Alt + F11' on your keyboard to open the Microsoft Visual Basic for Applications window.

Insert a New Module

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

Paste the Macro Code

Copy and paste the following code into the module window: Sub Red2Blue() Dim rng As Range, cel As Range, i As Long On Error Resume Next Set rng = Cells.SpecialCells(xlCellTypeConstants, xlTextValues) On Error GoTo 0 If rng Is Nothing Then Exit Sub Application.ScreenUpdating = False For Each cel In rng For i = 1 To cel.Characters.Count If cel.Characters(i, 1).Font.Color = vbRed Then cel.Characters(i, 1).Font.Color = vbBlue End If Next i Next cel Application.ScreenUpdating = True End Sub

Run the Macro

Close the VBA editor. Press 'Alt + F8' in your Excel workbook to open the Macro dialog, select 'Red2Blue' from the list, and click 'Run'.

Use a VBA Macro to Scan and Change Red Characters

Scope of the Macro: This script specifically targets text constants (xlCellTypeConstants, xlTextValues). It will not affect text generated by formulas or other non-text content in your worksheet.

Advanced Spreadsheet Editor

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office Spreadsheet provides excellent support for VBA macros, allowing you to automate repetitive tasks like changing specific text colors character-by-character with ease.

1. Open Your Workbook: Launch WPS Spreadsheet and open your .xlsx or .xlsm file containing the mixed-color text.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click on 'VBA Editor'.
3. Insert and Paste the Code: Click 'Insert' > 'Module' in the VBA window, and paste your color-changing macro script.
4. Execute the Script: Click the 'Run' button (or press F5) to execute the macro and instantly update your text colors.

Full support for running custom VBA macros and scriptsSeamless compatibility with Microsoft Office .xlsm and .xlsx file formatsLightweight application with fast, reliable performanceIntuitive developer tools for easy code editing

microsoft office alternative - wps office

Frequently Asked Questions

Can I change partial text color in Excel without using VBA?

You can manually highlight specific characters within a cell by double-clicking the cell, selecting the text, and changing the font color in the Home tab. However, for hundreds of cells, there is no built-in automatic tool to do this conditionally; a VBA macro is required for automation.

Why didn't the macro change the red text inside my formulas?

The macro provided uses 'xlCellTypeConstants', which explicitly targets cells containing typed text values. Formulas cannot have character-by-character color formatting applied to their outputs in Excel; the entire formula result must be a single format.

How can I modify this macro to change other colors?

You can change the target and replacement colors by modifying 'vbRed' and 'vbBlue' in the script. For example, to change green text to yellow, replace 'vbRed' with 'vbGreen' and 'vbBlue' with 'vbYellow'. For custom colors, you can use RGB values like 'RGB(255, 0, 0)'.

Excel & Data Problems