__Sum By Color and Count By Color using VBA in Microsoft Excel __

Enter the following code into VBA.

Function SumByColor(CellColor As Range, SumRange As Range)

Application.Volatile

Dim ICol As Integer

Dim TCell As Range

ICol = CellColor.Interior.ColorIndex

For Each TCell In SumRange

If ICol = TCell.Interior.ColorIndex Then

SumByColor = SumByColor + TCell.Value

End If

Next TCell

End Function

To use the function type **=sumbycolor( B1,A1:A10)**
- where B1 is the cell reference that contains the background color that you
want to count and A1:A10 is the sum range.

*Tip* - The cell reference containing the color can be the same as the cell reference containing the formula.

*Caution* - The formula does not automatically recalculate when you apply a new
interior color to a cell - however, I have made this custom function *Volatile* which means it will recalculate every time a cell value changes anywhere within the workbook.

With only a slight modification to the VBA code you can alter this custom function
to count by color.

The VBA code for a custom function that will count by color is:

Function CountByColor(CellColor As Range, CountRange As Range)

Application.Volatile

Dim ICol As Integer

Dim TCell As Range

ICol = CellColor.Interior.ColorIndex

For Each TCell In CountRange

If ICol = TCell.Interior.ColorIndex Then

CountByColor = CountByColor + 1

End If

Next TCell

End Function

You can download a sample spreadsheet here. The spreadsheet contains the VBA code for both the sum by color and count by color custom functions.

Return to Excel Exchange homepage.