Sum Cells with colors

[Guest]

What formula can I use to sum at the bottom of the pages specific colors?

ex.

5.00 yellow
2.00 green
1.00 yellow
1.00 red
3.00 green

total green 5.00
Total Yellow 6.00
Total Red 1.00

Is this possible?

Thank you ! You are all so helpful.

 

[willwonka]

You can create a custom function using Chuck Pearson's website:

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

Or

I believe the new ASAP Utilities has a function for this.

http://www.asap-utilities.com/

 

[Sandy]

Try playing around with this to get you started:

Sub ColorSum()
Dim mYellow, mGreen, mRed As Double
Dim mCell As Range
For Each mCell In Range("A1:A5")
If mCell.Interior.ColorIndex = 10 Then
[A7].Value = [A7].Value + mCell.Value
ElseIf mCell.Interior.ColorIndex = 6 Then
[A8].Value = [A8].Value + mCell.Value
ElseIf mCell.Interior.ColorIndex = 3 Then
[A9].Value = [A9].Value + mCell.Value
End If
Next
End Sub




Sandy

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Great Thank you ! These are just what I'm looking for. I do some reading
and I'm sure I will find the answer.

Thanks again ! You all are the best ! : )

 

[Guest]

I need a little more help.

I wanted to find out the color index of the colors I am using. In column B
I put the formula =colorindex(a2) the results was #name?

What did I do wrong?

 

[Guest]

I printed both the Pearson Software page and the Processing Coloured Cells
pages. I've used VBA with macros but I'm unfamiliar with this. I'm in the
VBA help. So, I'm still researching. Under ChartColorFormat there is an
example that sets the interior color of a range. However, I want to get the
list of color #'s. How can I get this?

 

[Sandy]

Open the VBE (Visual Basic Editor - Alt+F11) Click on help and search
for "Color Index" then click on "PatternColorIndex Property"

Sandy

 

[Guest]

Great Thanks,

I put in the formula : =SUMPRODUCT(--(colorindex(C7:C22,FALSE)=8),C7:C22) I
still get #name?.

What am I missing?

 

[Gord Dibben]

Chip's colorindex function is to be copied to a general module in the workbook.

Is that where you have it?


Gord Dibben MS Excel MVP