Here are two functions that will Sum and Count cells based on color.
In each function, RR is the range to test, ColorIndex is the color
index value to test for, and OfText indicates whether to test the font
color (OfText = True) or the background fill color (OfText = False).
Note that simply changing a cell's formatting or color does not cause
a calculation so you will need to force a calculation to ensure the
values are correct.
Function SumColor(RR As Range, ColorIndex As Long, _
OfText As Boolean) As Double
Dim R As Range
Dim D As Double
For Each R In RR.Cells
If IsNumeric(R.Value) Then
If OfText = True Then
If R.Font.ColorIndex = ColorIndex Then
D = D + R.Value
End If
Else
If R.Interior.ColorIndex = ColorIndex Then
D = D + R.Value
End If
End If
End If
Next R
SumColor = D
End Function
Function CountColor(RR As Range, ColorIndex As Long, _
OfText As Boolean) As Double
Dim R As Range
Dim D As Double
For Each R In RR.Cells
If OfText = True Then
If R.Font.ColorIndex = ColorIndex Then
D = D + 1
End If
Else
If R.Interior.ColorIndex = ColorIndex Then
D = D + 1
End If
End If
Next R
CountColor = D
End Function
You can then call these functions from worksheet cells with formulas
like
=SumColor(A1:A100,3,FALSE)
to sum all entries in A1:A100 that have a background color of red (3).
=CountColor(A1:A100,3,TRUE)
to count all the entries in A1:A100 that have a font color of red (3).
See Help for a list of color index values and their corresponding
color.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com