» Sum by color using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions It's not possible to add the values in a range depending on each cells background color with the built-in SUM()-function.
With the custom function below you can add the contents of cells depending on their background color:

Function SumByColor(InputRange As Range, ColorRange As Range) As Double
' returns the sum of each cell in the range InputRange that has the same
' background color as the cell in ColorRange
' example: =SumByColor($A$1:$A$20,B1)
' range A1:A20 is the range you want to sum
' range B1 is a cell with the background color you want to sum
Dim cl As Range, TempSum As Double, ColorIndex As Integer
    ' Application.Volatile ' this is optional
    ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
    TempSum = 0
    On Error Resume Next ' ignore cells without values
    For Each cl In InputRange.Cells
        If cl.Interior.ColorIndex = ColorIndex Then 
            TempSum = TempSum + cl.Value
        End If
    Next cl
    On Error GoTo 0
    Set cl = Nothing
    SumByColor = TempSum
End Function

The function is used in the same way as the built-in worksheetfunctions.
InputRange contains the cells with the values that the function is going to add,
ColorRange is a cell reference to a cell containing the background color you want restrict the adding to.
In Excel 5/95 the variables InputRange and ColorRange must be declared as Variant instead of Range.
The function can easily be adapted for use with other worksheetfunctions that you want to use on cells
with different background colors.