Excel VBA: Sum Cells by Fill Color

VBA & macros · Updated November 19, 2026

If by “add cells” you mean total their numbers, a VBA macro can sum values in a range when each cell’s fill color matches a sample cell. The example below checks ordinary cell fill colors, adds matching numeric values, and writes the result to a chosen output cell.

What adding cells by color means in Excel

Excel’s normal SUM function adds numbers based on cell addresses, not formatting. A fill color is formatting, so there is no built-in SUMIF criterion such as “red fill.” VBA can inspect each cell’s Interior.Color property, compare it with a reference cell, and add the values of matches. This is useful for a one-off summary or a workbook where color has a consistent meaning, such as marking approved expenses or completed quantities.

Decide what the color represents before relying on the total. Cell color is easy for people to see, but it is not inherently data: someone can change a fill without changing the underlying status. For workbooks used for reporting, filtering, or audit trails, a status column such as Approved or Pending is usually a clearer source for calculations. Color can still provide a visual cue while the status field supplies the dependable criterion.

Prepare the range and choose a color sample

In the example, values are in B2:B100, D1 is a sample cell whose fill indicates the color to match, and D2 is where the result will appear. Change those addresses to fit your sheet. The macro compares fill color, not font color, border color, or the text displayed in a cell. It includes numeric constants and formula results that are numeric; cells containing errors are skipped.

  • Put the numbers you want checked in one continuous range, or edit the code to use a different range.
  • Give D1 the fill color to match. You can copy a cell with the desired fill and use Paste Special > Formats, or choose a fill from Home > Fill Color.
  • Keep D2 outside the cells being summed, so the macro does not include its previous result in the next run.

The sample does not have to contain a number. It supplies a color only. If you choose a cell with no fill, cells whose fill is also effectively no fill can match. For clarity, use a deliberate, visible fill rather than relying on the default appearance of blank cells.

VBA macro to sum matching fill colors

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste this procedure into the module. It uses the active worksheet, checks B2:B100 against D1, and writes the total to D2.

Sub SumByFillColor()
    ' Sum numeric values whose fill matches the sample cell.
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim colorSample As Range
    Dim outputCell As Range
    Dim cell As Range
    Dim total As Double

    Set ws = ActiveSheet
    Set dataRange = ws.Range("B2:B100")
    Set colorSample = ws.Range("D1")
    Set outputCell = ws.Range("D2")

    For Each cell In dataRange.Cells
        If cell.Interior.Color = colorSample.Interior.Color Then
            If Not IsError(cell.Value) Then
                If Not IsEmpty(cell.Value) _
                   And VarType(cell.Value) <> vbBoolean Then
                    If IsNumeric(cell.Value) Then
                        total = total + CDbl(cell.Value)
                    End If
                End If
            End If
        End If
    Next cell

    outputCell.Value = total
End Sub

The macro starts total at zero, checks each cell in the specified range, and adds a matching value only when it is numeric and not an error or Boolean. Numeric text, such as text containing 12, can pass IsNumeric and be converted to a number; ordinary labels and empty cells do not add to the result. If your data should include only numbers actually stored as numbers, clean or validate the source data rather than assuming every numeric-looking entry has the intended meaning.

Run the macro and adapt its addresses

Return to the worksheet, press Alt+F8, select SumByFillColor, and choose Run. The macro places the result in D2. For example, if B2:B5 contains 10, 7, 4, and 9, and the first, third, and fourth cells match D1’s fill, the result is 23. Change the range and output assignments in the code to match your layout; use a fully qualified worksheet reference if the macro must always work on one named sheet instead of whichever sheet is active.

For example, replace the active-sheet assignment with Set ws = ThisWorkbook.Worksheets("Sales") when the target tab is named Sales. Then keep the range references attached to ws, such as Set dataRange = ws.Range("B2:B100"). This reduces the risk of running the procedure on the wrong active tab. A sheet name must match the workbook tab exactly, including spaces.

The macro runs only when you run it. If you edit a value, change a fill, or add a row afterward, run it again to refresh the result. The code uses a fixed range, so a new row below row 100 is not included unless you expand the address or revise the code to find the last used row.

Fill color, theme color, and conditional formatting

The code compares Interior.Color, which represents the cell’s applied fill color. It works with standard colors and theme colors as they appear on the cell; it does not compare the name of a theme swatch. Two cells that look similar may still have different color values if one uses a different shade or a different source of formatting. If a cell was formatted using a theme color, copying its format to the sample cell is a reliable way to select the same fill.

Conditional formatting needs special care. Interior.Color reads the cell’s underlying fill, not necessarily the color currently displayed because a conditional formatting rule is active. A cell may look red on screen while its ordinary fill is blank. In a regular macro, you can compare the displayed color by changing both sides of the comparison to cell.DisplayFormat.Interior.Color and colorSample.DisplayFormat.Interior.Color. DisplayFormat is intended for use in a macro or procedure; it is not a dependable way to create a worksheet UDF that recalculates whenever formatting changes.

If conditional formatting is driving the color, it is often better to total according to the rule’s underlying condition. For instance, if a cell turns red when its value is below a threshold, calculate the total using that numeric condition rather than trying to infer the rule from the displayed color. This makes the result easier to check and less vulnerable to a formatting change.

Refresh behavior and recalculation limits

A common request is to make a formula such as a custom SUM-by-color function update instantly when someone paints a cell. Formatting changes do not behave like ordinary cell-value changes in Excel’s calculation engine, so a user-defined VBA function may not recalculate just because a fill was changed. A macro avoids presenting itself as a live formula: run it again after changing colors or values. You could add a button linked to the macro, but the result still represents the sheet at the time the button was pressed.

For a frequently updated worksheet, consider storing the category in a column and using a normal formula or PivotTable based on that category. For example, values in B2:B100 and labels such as Red or Blue in C2:C100 can be summarized by label with standard worksheet functions. The exact formula depends on the workbook’s layout, but this data-based approach updates through normal recalculation and is easier to review than a color-only rule.

Common problems and practical fixes

  • The result is zero: Check that the sample and target cells truly have the same fill, that the range points to the intended cells, and that the matching cells contain numeric values rather than labels or errors.
  • The displayed color matches but is not counted: The color may come from conditional formatting. Use DisplayFormat in a macro, or preferably base the calculation on the condition behind the formatting.
  • The result misses new rows: The example deliberately uses a fixed range. Expand B2:B100 or change the procedure to determine the last data row for your sheet.
  • The wrong worksheet changes: The sample uses ActiveSheet. Activate the intended sheet before running, or set ws to a specific worksheet as shown above.
  • The total seems too high: Check for numeric text, duplicate entries, or a sample color that also matches cells you did not intend to include. The macro adds every matching numeric value in its range.
  • The macro cannot be found: Confirm it is in a standard module and that the workbook is saved in a macro-enabled format.

To make the routine easier to maintain, keep the sample cell and output cell labelled, and document the meaning of the fill color next to the data. If different sheets use different ranges, consider separate procedures or a carefully designed shared routine rather than editing addresses without checking which cells are included.

Saving, platform support, and macro safety

Save the workbook as Excel Macro-Enabled Workbook (*.xlsm) if you want to keep the VBA code. A standard .xlsx file does not retain VBA macros. In Microsoft 365 for Windows, you can show the Developer tab through File > Options > Customize Ribbon, then check Developer. Excel for Mac also supports VBA, although the editor and ribbon settings differ; open the Visual Basic Editor from the Tools > Macro menu or use the Developer tab if it is enabled. On Mac, keyboard shortcuts can depend on the keyboard and system settings, so the menu command is a reliable alternative to the Windows Alt+F11 shortcut.

Excel for the web cannot create, edit, or run VBA macros. Open the workbook in the desktop Excel application to use this procedure. Treat macros as executable code: only enable macros in files from sources you trust, and do not lower security settings just to run an unknown workbook. Before running a macro, save a copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z, so a backup is important if the code writes to cells or changes workbook content.

When a non-VBA method is a better choice

If color is only a way to mark rows temporarily, Excel’s filter by color can help you inspect matching records, but filtering alone does not create a robust automatic total by fill. A helper column containing a category is more suitable for recurring summaries, shared workbooks, and data that may be imported or audited. Conditional formatting can then display colors based on that category, keeping the visual signal and the calculation tied to the same underlying value.

Use the VBA method when the color itself is already the information you need to summarize and a manually refreshed result is acceptable. Use a status field and ordinary formulas when the result must stay current, be understood by other users, or survive changes to the workbook’s visual design.

Frequently Asked Questions

Can Excel sum cells based on fill color without VBA?
Excel has no built-in SUMIF condition for a manually applied fill color. For a repeatable calculation, a helper column describing the category is usually preferable; VBA can inspect the formatting when color is the only available marker.

Does this macro count cells colored by conditional formatting?
Not with the Interior.Color comparison in the main example, because that property checks the underlying fill. In a macro, compare DisplayFormat.Interior.Color to inspect the displayed conditional-format color, or calculate from the rule’s underlying condition instead.

Will the total update when I change a cell’s color?
No. Run the macro again after changing a fill or a value. A formatting change alone does not reliably trigger recalculation of a VBA worksheet function.

Can I sum by font color instead of fill color?
Yes. In a macro, compare each cell’s Font.Color with the sample cell’s Font.Color rather than comparing Interior.Color. Conditional formatting can also affect displayed font color, so use DisplayFormat.Font.Color when the visible font color is produced by a conditional-formatting rule.

Can I run this VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in the desktop Excel application for Windows or Mac, and save it as an .xlsm file to preserve the macro.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.