fn_Sum_by_Color: Sum Cells by Fill Color

Overview

Excel VBA UDF

A custom worksheet function that sums all cells in a range whose fill (background) color matches a reference cell. Ideal for color-coded financial models where totals need to be grouped by cell shading.

How It Works

Cell_Color: A single cell whose fill color is used as the matching criterion.
Sum_Range: The range of cells to check and sum.
Uses Interior.ColorIndex to compare fill colors, then sums matching cells.
Marked Application.Volatile so it recalculates when colors change.

Usage

Enter in any cell:

=fn_Sum_by_Color(A1, B1:B100)

Where A1 contains the reference fill color and B1:B100 is the range to sum.

The VBA Code

Function fn_Sum_by_Color(Cell_Color As Range, Sum_Range As Range)
    Application.Volatile
    Dim Clr_Sum As Double
    Dim Clr_Idx As Integer: Clr_Idx = Cell_Color.Interior.ColorIndex
    Dim Clr As Range
    For Each Clr In Sum_Range
        If Clr.Interior.ColorIndex = Clr_Idx Then
            Clr_Sum = WorksheetFunction.Sum(Clr, Clr_Sum)
        End If
    Next Clr
    fn_Sum_by_Color = Clr_Sum
End Function