Sum of cells depending on cell colour formatting

Hi All,

I recently came out with a problem using the sum function.
Imagine that you have a range of cells and you used a background color to identify certain specific cells and you wish to calculate the sum of only the cells with, for example, the red background in the range.
How can this problem be sorted out? Here the solution:

Function SumColor(DefClr As Range, DefRng As Range)

Dim MyCell As Range
Dim MyCol As Long
Dim SumColCell

'Identify the colored cell
MyCol = DefClr.Interior.ColorIndex

'Sum the colored cells in the range
For Each MyCell In DefRng
If MyCell.Interior.ColorIndex = MyCol Then
SumColCell = WorksheetFunction.SUM(MyCell, SumColCell)
End If
Next MyCell

SumColor = SumColCell

End Function


The VBA formula above as two parameters. With the first one you identify a colored cell in the range that you want to sum, the second is the range that you want to sum.

E.g.

Let's assume that this is what you have on column A:

A1. with red background

A2. with red background

A3. with yellow background

A4. cell with no background

A5. cell with no background

If you want to know the sum of only the red background cells, proceed as follow:

enter in A6 =SumColor(A1,A1:A5)

Enjoy