VBA & Macro Problems

How to Use VBA to Find Excel Cells with Red Font Formatting

Elise Williams Oct 9, 2026 872 views

Question details

The user needs to identify and count cells that contain manually applied red font formatting across a large range of data in Excel.

How to Use VBA to Find Excel Cells with Red Font Formatting

Product: Excel
Device & OS: not provided
Scenario: When working with extensive datasets, users often need to extract, flag, or count data formatted with a specific text color (e.g., red) to streamline their data analysis tasks.
Observed behavior: A standard Excel formula cannot natively count cells based on font color, requiring the implementation of a custom VBA user-defined function to iterate through the range and return the count based on a reference cell's color index.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) so you do not lose your custom VBA code, and verify that the Developer tab is enabled in your ribbon.

Solution 1Recommended

Create a VBA User-Defined Function (UDF) to Count Red Font Cells

Use the VBA Editor to create a custom function that compares the font color of cells in a specific range against a reference cell, counting how many perfectly match.

Because Excel does not have a built-in formula for analyzing cell formatting, you must create a custom function using Visual Basic for Applications (VBA). This function will read the ColorIndex property of the font in your target cells.

Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) Editor window.

Insert a New Module

In the VBA Editor, click on 'Insert' from the top menu bar and select 'Module' to create a blank workspace for your new code.

Paste the Custom Function Code

Copy and paste the following code into the module window: Function AnyRed(rngCells As Range, rngRef As Range) As Integer Dim c As Range AnyRed = 0 For Each c In rngCells If c.Font.ColorIndex = rngRef.Font.ColorIndex Then AnyRed = AnyRed + 1 Next c End Function

Prepare a Reference Cell

Close the VBA Editor and return to your spreadsheet. Select cell A1 (or any empty cell) and manually format its text with the exact red color you want the function to find.

Apply the Custom Formula

In the cell where you want the result to appear, type the formula =AnyRed(B2:Z2,$A$1). Modify the range B2:Z2 to match the data you are checking, and ensure $A$1 points to your reference cell. Press Enter to display the total count of red-font cells.

Create a VBA User-Defined Function (UDF) to Count Red Font Cells

Manual Formatting Limitation: This specific VBA function only detects font colors that were applied manually. It will not identify or count cells whose red font color is applied via Conditional Formatting.

Advanced Data Management

Easily Run Macros and Custom Functions in WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for VBA macros and custom user-defined functions. You can seamlessly use scripts to count font colors, automate tasks, and manage large datasets exactly as you would in Microsoft Excel.

1. Open Your Workbook: Launch WPS Office and open your existing spreadsheet data.
2. Access the Developer Tools: Navigate to the 'Developer' tab in the top ribbon and click on 'VB Editor' to open the coding environment.
3. Insert Your VBA Code: Click Insert > Module, paste your counting script, and use the custom formula immediately in your active worksheet.

Fully compatible with Microsoft Excel file formats, including Macro-Enabled Workbooks (.xlsm).Robust, built-in VBA environment to run custom functions without compatibility issues.Lightweight, fast performance perfectly suited for analyzing large ranges of data.Free and intuitive interface that minimizes the learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the VBA formula return 0 even when there are red font cells?

This happens if the red font color was applied using Conditional Formatting rather than manually. The provided VBA script uses the 'Font.ColorIndex' property, which only reads manual formatting. To read conditional formats, you would need a more advanced script utilizing the 'DisplayFormat' property.

Can I modify this VBA code to count cell background colors instead of font colors?

Yes. To count background fill colors, open the VBA Editor and replace 'c.Font.ColorIndex' with 'c.Interior.ColorIndex' and 'rngRef.Font.ColorIndex' with 'rngRef.Interior.ColorIndex' in the code. Make sure your reference cell is filled with the correct background color.

Will the count update automatically if I change a cell's font color to red later?

No, changing a cell's formatting (like font color) does not automatically trigger Excel to recalculate formulas. To update the count after changing a cell's color, you must force a recalculation by pressing F9 or double-clicking the formula cell and pressing Enter.

Excel & Data Problems