Macros to count cells with specific color and text

[cammurali]

Hi All

I have two work sheets in an excel document. in the second sheet i have few
cells in a column in four colors. i would like to count cells with these four
colors and put the results in four different cells of first work sheet. The
colors are Red, Green, yellow and White. I can specify the range of cells in
which the count has to be performed.

please help me with this task. I need this to make my documentation work easy.

regards

 

[Mike H]

Have a look here

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

Mike

 

[cammurali]

Hi Mr. Mike

Thankx for you response.

To be honest with you i am not good in programming. I have a very basic
knowledge. Looks like the information send by you might be useful but i am
not sure how could i use it to solve my problem

 

[Mike H]

Hi,

Here's a very simple UDF to count cell font or background colours. To instal
it press Alt +F11 to open VB editor. Double click 'This Workbook' and paste
it on on the right

Function CountColor(Rng As Range, clr As Integer) As Integer
Dim C As Range
For Each C In Rng
'If C.Font.ColorIndex = clr Then
If C.Interior.ColorIndex = clr Then
CountColor = CountColor + 1
End If
Next C
End Function


Now you don't make it clear whether you want to count font colour or
interior color so at the moment it will count interior colours. If you want
Font then remove the ' from in front of
'If C.Font.ColorIndex = clr Then
and put a ' in front of
If C.Interior.ColorIndex = clr Then
Close VB editor.

call the function with
=CountColor(A1:A27,3)
or for another sheet
=CountColor(Sheet2!A1:A27,3)

Where 3 is red and 4,2 or 6 are (probably) your other colours. i say
probably because I can't know which green or yellow you used.

Mike

 

[Bob Phillips]

See http://xldynamic.com/source/xld.ColourCounter.html

 

[JLGWhiz]

Mike's code will work OK if your color is set by regular format. However, if
it is set by Conditional Format then you will not get a good count. Counting
colored cells is not a very reliable procedure.

 

[Bob Phillips]

If it is CF colour, you won't get any count, never mind a good count. But it
can be done, by other means.

 

[JLGWhiz]

Hi Bob, yes, I have seen code that checks to see if the condition is met
before it counts or returns data from the cell. It still seems to me to be
an unreliable, or at least complex, method when colors can be displayed by
more than one method in the cells and no single detection method can be used
to identify those cells. I suppose that in some cases, there might be no
other way to retrieve the data, but I always try design my projects to avoid
that trap.

 

[Bob Phillips]

I agree it is complex, but unreliable? I wouldn't say so. If there could be
CF colours and fill colours you can combine the code for both types.
Admittedly, you would have to have a rule to determine which takes
precedence, but that is a business decision, nothing to do with code per se.