Adding ONLY cells of certain color

  • Thread starter Kentucky Insurance
  • Start date
K

Kentucky Insurance

Hello!
Can anyone tell me how to add only cells of a certain color? Whether it's
the cell's background color, or the font color, doesn't really matter. I
researched and seemed like SumIF was the correct function, but I can't figure
out how to specify only certain colors.....
Thanks for your help.
 
G

Gary''s Student

Enter this UDF:

Function SumRed(r As Range) As Long
Dim rr As Range
SumRed = 0
For Each rr In r
If rr.Interior.ColorIndex = 3 Then
SumRed = SumRed + rr.Value
End If
Next
End Function

and use it like:
=SumRed(A1:G4)

It will sum those cells that have a red background. If you change the
cell's background after you have entered the formula, you will need to
manually re-calculate the worksheet:

CNTRL-ALT-F9
 
Top