Adding sums only in certain colored cells

[vkauahi]

I don't know if this is possible, but would greatly help me out if it is.
I want to know if it's possible to add the sums of certain color cells only.
For example, when I color code my sums green, that means I have it in my
account. Other colors mean other things...I want it to add it up
automatically when I change it to green. Hope that makes sense. Is this
possible? Any help would be greatly appreciated.
Thank!
VK

 

[Gord Dibben]

If cells are colored due to Conditional Formatting, use the same criteria(on)
that was used to format them.

If manually colored you will need VBA to SUM them by color.

See Chip Pearson's site for Functions.

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


Gord Dibben MS Excel MVP

 

[vkauahi]

It is manually colored, now I have to figure out the VBA bit...(new to excel)
Thanks so much for your help. I'm so glad I found this site.
VK

 

[Gord Dibben]

Copy Chip's SumByColor Function to a general module in your workbook.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to Excel window.

Enter the formula as such....=SumByColor(cellref,indexnumber,true or false)

Indexnumber is that of the background color or the font color.

If looking to sum by BG color use FALSE as argument.

If looking to sum by font color use TRUE as argument.

For a list of colorindex numbers see David McRitchie's site

http://www.mvps.org/dmcritchie/excel/colors.htm


Gord

It is manually colored, now I have to figure out the VBA bit...(new to excel)
Thanks so much for your help. I'm so glad I found this site.
VK

Gord Dibben MS Excel MVP

 

[Gord Dibben]

To where are you pasting the code?

It must go into a General Module in the workbook you are using.

If in another workbook like Personal.xls you must preface with the file name.

=Personal.xls!sumbycolor(H87:H89, 35, FALSE)


Gord Dibben MS Excel MVP