VBA & Macro Problems

How to Sum Excel Cells by Red Font Color Using VBA

Bushra Parveen Oct 1, 2026 870 views

Question details

The user needs to sum numeric values in a specific range of cells based exclusively on the font color being red.

How to Sum Excel Cells by Red Font Color Using VBA

Product: Excel
Device & OS: not provided
Scenario: Calculating a total sum for a range of cells (C1:C12) where only the numbers formatted with red text should be included in the final calculation.
Observed behavior: Standard Excel worksheet formulas cannot detect or calculate sums based on cell formatting, such as font color.

Before you start

Ensure that the cells you want to sum contain numeric values and that the font color applied is standard red, as the macro specifically evaluates against the default vbRed color code.

Solution 1Recommended

Sum Cells by Red Font Color Using a Custom VBA Macro

Since standard formulas do not recognize text color, creating a simple VBA macro is the most effective and reliable way to sum cells based on their font color.

Excel does not have a native formula (like SUMIF) that can read font colors or background colors. By using a short Visual Basic for Applications (VBA) script, you can iterate through a specified range, check the font color property of each cell, and add up the values.

Open the Visual Basic Editor

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

Insert a New Module

In the top menu of the VBA Editor, click 'Insert' and then select 'Module' to create a new blank module for your code.

Paste the VBA Code

Copy and paste the following code into the module window: Sub SumRedFontCells() Dim cell As Range Dim total As Double For Each cell In ActiveSheet.Range("C1:C12") If cell.Font.Color = vbRed Then total = total + cell.Value Next cell MsgBox "The sum of cells with red font is: " & total End Sub

Run the Macro

Close the VBA Editor to return to your worksheet. Press Alt+F8 to open the Macro dialog box, select 'SumRedFontCells' from the list, and click 'Run'.

Sum Cells by Red Font Color Using a Custom VBA Macro

Macro Execution Successful: A message box will instantly appear displaying the total sum of all cells in the range C1:C12 that have red text.

Use VBA in WPS Spreadsheet

Run Macros to Sum Formatted Cells Easily in WPS Office

WPS Spreadsheet fully supports VBA macros, allowing you to use the exact same code to sum cells by font color or background color just like in Microsoft Excel.

1. Open WPS Spreadsheet: Launch WPS Office, open your spreadsheet, and navigate to the 'Developer' tab in the top ribbon.
2. Access the VBA Editor: Click on 'Visual Basic Editor' (or simply press Alt+F11) to open the macro environment.
3. Insert and Run Code: Insert a new Module, paste your VBA code for summing red font cells, and press F5 to run it.

Fully compatible with Microsoft Excel VBA macros and .xlsm files.Free and lightweight spreadsheet alternative with high performance.Familiar user interface makes it easy to locate and use the Developer tools.Powerful data analysis and formatting tools built right in.

microsoft office alternative - wps office

Frequently Asked Questions

Can I sum cells by background color instead of font color using VBA?

Yes. You can modify the VBA code to check the background fill by replacing 'cell.Font.Color' with 'cell.Interior.Color'. For example, 'If cell.Interior.Color = vbRed Then' will sum cells that have a red background.

Will a standard SUMIF formula work if I use Conditional Formatting?

No, the standard SUMIF function cannot detect colors applied by conditional formatting or manual formatting. However, you can use SUMIF based on the same underlying logical condition that triggers your conditional formatting.

How do I change the range of cells the macro checks?

In the provided VBA code, locate the line 'For Each cell In ActiveSheet.Range("C1:C12")'. Change the "C1:C12" reference to your desired cell range, such as "A1:A100" or "D5:D50".

Why does the macro show a sum of 0 even though I have red text?

Ensure the font color applied is exactly standard red (vbRed). If you selected a slightly different shade of red, a custom color, or a theme color from the palette, the vbRed condition in the macro will not recognize it.

Excel & Data Problems