Excel VBA: Sum Cells by Fill Color

VBA & macros · Updated November 19, 2026

To sum numbers in cells with a particular fill color, add a VBA function to a standard module and call it with the values range and a sample cell of the color to match. For example, if the numbers are in B2:B20 and E1 has the fill color you want, use =SumByFillColor(B2:B20,E1).

What summing by fill color means

Excel’s built-in SUM function adds values based on cell contents and references; it has no argument for fill color. A VBA function can check each cell’s formatting, compare its fill with a sample cell, and add the matching values. This is useful when color is part of a manually maintained workflow, such as marking approved expenses in green or flagging a category of inventory.

Color is formatting, not data. A green fill does not tell Excel why a cell is green, and changing the fill does not change the cell’s value. That distinction matters: a color-based total can be convenient, but it is less transparent and dependable than summing by a category, status, or other value stored in the worksheet.

How the VBA function works

The function below reads the fill color from the first cell of the sample reference. It then checks each cell in the range to total, adding a value when that cell’s ordinary fill color matches. It skips error values and nonnumeric text. Numeric-looking text, such as text containing "12", is treated as numeric by VBA’s IsNumeric test and will be added as 12.

Public Function SumByFillColor(ByVal sumRange As Range, ByVal sampleCell As Range) As Double
    Dim targetColor As Long
    Dim oneCell As Range
    Dim total As Double

    ' Recalculate when Excel performs a calculation; a fill change alone may not trigger it.
    Application.Volatile
    targetColor = sampleCell.Cells(1, 1).Interior.Color

    For Each oneCell In sumRange.Cells
        If oneCell.Interior.Color = targetColor Then
            If Not IsError(oneCell.Value2) Then
                If IsNumeric(oneCell.Value2) Then
                    total = total + CDbl(oneCell.Value2)
                End If
            End If
        End If
    Next oneCell

    SumByFillColor = total
End Function

For a worksheet where B2:B20 contains the amounts and E1 is a cell filled with the color to match, enter this formula in a result cell outside those ranges: =SumByFillColor(B2:B20,E1). If the matching cells contain 10, 15, and 8, the result is 33. The sample cell’s own value is not included unless it is also part of the sum range.

Add the function to an Excel workbook

  1. Save a copy of the workbook first. VBA macros cannot be undone with Ctrl+Z, and code or macro-enabled files may be blocked by security settings.
  2. Open the Visual Basic Editor. On Windows, use Alt+F11 or choose Developer > Visual Basic. If Developer is not visible, enable it through File > Options > Customize Ribbon.
  3. In the editor, choose Insert > Module, then paste the function into the module. A standard module is important; do not paste it into a worksheet’s code window or ThisWorkbook.
  4. Return to Excel, enter the formula in a worksheet cell, and press Enter. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) to retain the VBA code.

On a Mac, open the Visual Basic Editor using Developer > Visual Basic, or the available Option+F11 shortcut depending on the keyboard settings. Enable the Developer tab from Excel > Settings (or Preferences) > Ribbon & Toolbar. Save as an .xlsm file there as well. Excel for the web can display a macro-enabled workbook, but it cannot create, run, or edit VBA macros; use desktop Excel for these steps.

Recalculation after a fill color changes

A key limitation is that Excel’s calculation engine tracks formula dependencies such as cell values, but a manual formatting change is not a normal formula dependency. Consequently, painting a cell a new color may leave the displayed total unchanged even though the function contains Application.Volatile. Volatile means the function recalculates when Excel recalculates; it does not guarantee recalculation immediately after a fill change.

After changing a fill, press F9 on Windows to recalculate formulas. On Mac, use the equivalent calculation command available in Excel, commonly F9 (on some keyboards, Fn+F9). If the result still appears stale, edit the formula and press Enter or use Formulas > Calculate Now. These steps request recalculation; they do not change which color the function reads.

For repeated work, consider using a macro that calculates and writes a value after the formatting is finished, or better, record the category in a cell and use a regular worksheet formula. A color-based formula is most appropriate when the color itself is intentionally the criterion and users understand the refresh limitation.

Direct fills and conditional formatting

The function uses Interior.Color, which checks the cell’s underlying fill formatting. It is suitable when a user applied the fill directly through the ribbon, such as Home > Fill Color. It does not necessarily represent the color currently visible on screen when conditional formatting controls the appearance.

Conditional formatting applies a visual format when a rule is true. For example, a rule may display a red fill for values below zero even though the cell’s ordinary fill is white. The function above checks that underlying white fill, not the conditional-format red. If you need to total by the displayed conditional-format color, use a macro procedure that reads DisplayFormat.Interior.Color after the rules have been applied. Do not put DisplayFormat into this worksheet UDF; it is not supported for that use and can return an error.

Here is an example macro for that situation. Change the worksheet name and ranges to fit the workbook. It reads the displayed fill of E1 as the target color, checks the displayed fills in B2:B20, and writes the total to F1. Keep F1 outside the range being summed.

Public Sub SumByDisplayedFillColor()
    Dim ws As Worksheet
    Dim sumRange As Range
    Dim sampleCell As Range
    Dim resultCell As Range
    Dim oneCell As Range
    Dim targetColor As Long
    Dim total As Double

    ' Adjust these references before running the macro.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set sumRange = ws.Range("B2:B20")
    Set sampleCell = ws.Range("E1")
    Set resultCell = ws.Range("F1")

    targetColor = sampleCell.DisplayFormat.Interior.Color
    For Each oneCell In sumRange.Cells
        If oneCell.DisplayFormat.Interior.Color = targetColor Then
            If Not IsError(oneCell.Value2) Then
                If IsNumeric(oneCell.Value2) Then
                    total = total + CDbl(oneCell.Value2)
                End If
            End If
        End If
    Next oneCell

    resultCell.Value = total
End Sub

This is a regular macro, not a worksheet function: run it from the Visual Basic Editor or the Macros dialog after the conditional formatting has updated. It writes a fixed result, so run it again after relevant values or formatting change. As with any macro, save a copy before running it.

Choosing a sample cell and matching colors

The sample cell is a convenient way to identify a color without typing a color number into the formula. Apply the desired direct fill to a spare cell and use that cell as the second argument. A change to the sample’s fill changes the color the function looks for on its next calculation. The function compares the actual color value, not a color name such as “green,” so two fills that look similar may not match if they use different RGB values or theme colors.

Choose a sample cell with a single, unambiguous fill. If it has no fill, Excel may report the default background color; cells that appear white may therefore match it. A white fill applied explicitly and a cell with no fill can also be hard to distinguish visually. Test the function with a small range and a few known examples before relying on a large total.

The code checks all cells in the supplied range, including cells with formulas and cells containing numbers stored as text. It sums numeric values only; labels and blank cells contribute nothing. If a range contains errors, those cells are skipped rather than stopping the total. These choices are deliberate, but they may differ from the exact behavior you expect from Excel’s built-in SUM.

Common problems and practical checks

  • The formula shows #NAME? Confirm that macros are available, the function is in a standard module, and the function name in the formula is spelled exactly as in the code. If the file was saved as .xlsx, VBA code may not have been retained; save as .xlsm.
  • The result does not change after recoloring. Recalculate with F9 or re-enter the formula. A formatting change alone may not trigger a UDF calculation.
  • Cells look the same but do not match. Check whether one color is from conditional formatting, a theme shade, or a different fill value. The supplied function compares underlying direct-fill color values.
  • The total is unexpectedly high. Check for numeric-looking text, a sample color that matches blank-looking cells, or a range that includes cells outside the intended data. The function adds numeric text after converting it to a number.
  • Macros are blocked. Use only files and code you trust, and follow your organization’s macro-security policy. Do not enable macros merely because a workbook asks you to.

Color-based totals versus better data-driven methods

Color can be meaningful to a person, but it is a fragile way to store business logic. A teammate may change a fill for appearance, paste formatting from elsewhere, or use a different shade. A formula that sums by a status column is easier to audit and usually recalculates predictably. For example, store “Approved” in column C and use SUMIF to total the corresponding amounts rather than relying on green fill.

If colors must be used, a VBA UDF is convenient for an automatically displayed total, subject to the refresh caveat. A macro using DisplayFormat is appropriate when the visible color—including conditional formatting—is what matters, but its result is a written value that must be refreshed by running the macro. For an occasional check without VBA, filter or sort the data using the relevant worksheet criteria where possible, or use a small helper column that explicitly identifies the intended category. These alternatives reduce dependence on formatting and are easier to share with people who cannot run macros.

Safety and workbook compatibility

VBA code runs with the permissions available to the workbook, so review code before enabling it and keep a backup. Ctrl+Z cannot reverse a macro’s actions. The first function only reads cells and returns a result, while the displayed-fill macro writes to F1; adjust that destination carefully so it does not overwrite source data.

Excel desktop for Microsoft 365 on Windows supports these VBA procedures. Desktop Excel for Mac also supports VBA, though the editor access and keyboard shortcuts differ. Excel for the web does not run VBA. A recipient who opens the workbook with macros disabled will not be able to use the custom worksheet function normally, so include a note about the macro requirement or provide a data-driven alternative when sharing the file.

Frequently Asked Questions

Can Excel sum cells by color without VBA?
Excel does not provide a standard SUM function that sums by fill color. For a durable solution, store the category in a cell and use a normal formula such as SUMIF. VBA is an option when the fill itself must determine the total.

Why does my VBA sum not update when I change a cell’s color?
Excel does not reliably treat a manual formatting edit as a dependency that triggers formula recalculation. Press F9 or re-enter the formula. Application.Volatile helps during calculation but does not force a calculation every time formatting changes.

Will the function count conditional-formatting colors?
No. The worksheet function checks the underlying fill with Interior.Color, not necessarily the displayed conditional-format fill. A regular macro can inspect DisplayFormat after the conditional-formatting rules have updated.

Does the sample cell have to contain a value?
No. The function uses the sample cell’s fill, not its contents. You can use an otherwise empty cell as a color swatch, provided its fill is the one you want to match.

Can I use this function in Excel for the web?
You can open a macro-enabled workbook in Excel for the web, but the web app does not run VBA or custom VBA worksheet functions. Use desktop Excel, or replace the color-based logic with a worksheet data field and a standard formula.

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.