How to add cells with a particular background color?
 

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?

How to add cells with a particular background color?
 

You would need to use VBA to accomplish this.

"BruceN1" wrote:

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?

How to add cells with a particular background color?
 

How difficult is this

"akphidelt" wrote:

You would need to use VBA to accomplish this.

"BruceN1" wrote:

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?

How to add cells with a particular background color?
 

Read instructions at Chip Pearson's site then copy the functions provided to a
general module in your workbook.

http://www.cpearson.com/excel/colors.htm

Or download the EasyFilter add-in from Ron de Bruin's site.

http://www.rondebruin.nl/easyfilter.htm


Gord Dibben MS Excel MVP

On Fri, 29 Feb 2008 11:39:03 -0800, BruceN1
wrote:

How difficult is this

"akphidelt" wrote:

You would need to use VBA to accomplish this.

"BruceN1" wrote:

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?

How to add cells with a particular background color?
 

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

"BruceN1" wrote:

How difficult is this

"akphidelt" wrote:

You would need to use VBA to accomplish this.

"BruceN1" wrote:

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?