Excel - Colourfunction

[technocraze]

Hi Guys,

May I know whether is there such function in excel whereby I will be able to track the number of coloured cells and sum it?

For example:
A1 - green coloured cell
A2 - orange coloured cell
A3 - green coloured cell
A4 - purple coloured cell
A5 - green coloured cell
A6 - purple coloured cell

Count
green = 3
orange = 1
purple = 2

I do know there is such function COUNTIF(A1: A6), but nt too sure hw to continue from there. Any help will be much appreciated.

[georgiboy]

I dont know of a function in excels frontend that will do this, you could use a loop in vba to count the cell colours.

Sub Macro1()
Dim MyRange As Range, rCell As Range
Dim red, orange, green As Integer
Set MyRange = Range("A1:A50")
For Each rCell In MyRange.Cells
    If rCell.Interior.Color = 255 Then red = red + 1
    If rCell.Interior.Color = 49407 Then orange = orange + 1
    If rCell.Interior.Color = 5287936 Then green = green + 1
Next
MsgBox "There are " & red & " red cells." & vbNewLine & "There are " & orange & " orange cells." _
& vbNewLine & "There are " & green & " green cells."
End Sub

you would need to change the range that has been set and the colours that have been used to suit.

I use Excel 2007 so i believe that the colour index's asr different

Hope this helps

[Bob Phillips]

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