How to add cells with a particular background color?

B

BruceN1

If I have a column with amounts and I color code these with different
colors...... How do I write a formula to add all the dark greens in one cell
and browns in another, and or yellows in another, etc?
 
T

Tom Hutchins

Here is a user-defined function which returns the colorindex of a single
cell. Call it like this to get the interior (fill) color: =GetColorIndex(B27)
or call it like this if you want the font color: =GetColorIndex(B27,"Font")
Use the function in an empty column to get colorindex of all your data
cells. Then you can use SUMIF to add all the cells which have the same
colorindex.

Paste the following code into a VBA module in your workbook. From the Tools
menu, select Macro, then Visual Basic Editor. In the Visual Basic Editor,
Select Project Explorer from the View menu. Probably along the left side of
your screen, you should see VBAProject followed by the name of your workbook.
Click on that, then select Module from the Insert menu. Paste the code in the
blank module that appears.

Public Function GetColorIndex(Target As Range, Optional WhichType As String)
As Variant
If Target.Count > 1 Then
GetColorIndex = "ERROR"
Exit Function
End If
If WhichType = "Font" Then
GetColorIndex = Target.Font.ColorIndex
Else
GetColorIndex = Target.Interior.ColorIndex
End If
End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hope this helps,

Hutch
 
Top