Sum of Colored Cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tester Testings - 08 Mar 2008 13:58 GMT

hi all,
suppose i have a range of cells, and that range needs to summed depanding on
the cells color.
for example, i need to sum the cells colored with green in one cell, and the
cells colored with red in another cell and so the blue ones.
is there any way to do that?

many thanks in advance.

Reply to this Message

Dave Peterson - 08 Mar 2008 14:18 GMT

You're going to need a macro.

Chip Pearson has lots at:
http://cpearson.com/excel/Colors.aspx

Look for SumColor on that page.

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

> hi all,
> suppose i have a range of cells, and that range needs to summed depanding on
[quoted text clipped - 4 lines]
>
> many thanks in advance.

Signature

Dave Peterson

Reply to this Message

Bob Phillips - 08 Mar 2008 14:54 GMT

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

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> hi all,
> suppose i have a range of cells, and that range needs to summed depanding
[quoted text clipped - 4 lines]
>
> many thanks in advance.

Reply to this Message

L. Howard Kittle - 08 Mar 2008 20:56 GMT

You can modify this to suit your needs I think. Name your range Data or to
suit and change code to match the named range.

Sub ColorCount()
'Counts the number of colored
'cells in a range named Data.
Dim Blue5 As Integer
Dim Red3 As Integer
Dim Green4 As Integer
Dim Yellow6 As Integer
Dim Cell As Range

For Each Cell In Range("Data") '("B1:F11")
If Cell.Interior.ColorIndex = 5 Then
Blue5 = Blue5 + 1
ElseIf Cell.Interior.ColorIndex = 3 Then
Red3 = Red3 + 1
ElseIf Cell.Interior.ColorIndex = 4 Then
Green4 = Green4 + 1
ElseIf Cell.Interior.ColorIndex = 6 Then
Yellow6 = Yellow6 + 1
End If
Next

Range("A1").Value = Blue5 & " Blue"
Range("A2").Value = Red3 & " Red"
Range("A3").Value = Green4 & " Green"
Range("A4").Value = Yellow6 & " Yellow"

MsgBox " You have: " & vbCr _
& vbCr & " Blue " & Blue5 _
& vbCr & " Red " & Red3 _
& vbCr & " Green " & Green4 _
& vbCr & " Yellow " & Yellow6, _
vbOKOnly, "CountColor"
End Sub

HTH
Regards,
Howard

> hi all,
> suppose i have a range of cells, and that range needs to summed depanding
[quoted text clipped - 4 lines]
>
> many thanks in advance.

Reply to this Message