How can I count coloured cells (coloured using Conditional Formatting)

Hello

I used Conditional Formatting to highlight cells containing specific values (i.e. less than, between, etc...). I now need to show a count of how many cells are green, red, yellow, etc...

I have been reading about having to create a User Defined Function using VBA, but after various tests, I can only achieve the desired results if I manually highlighted the cells (i.e. highlighted the cell and filled it with colour).

How can I count the cells which were highlighted using Conditional Formatting?

I am using Excel 2007.

Thanks,
Steph

You need to set up a similar formula as those you have used for conditional formatting. For example, if one of your conditions is to turn the cell green if it is less than 10, then to count the green cells you would need a formula like this:

=COUNTIF(cell_range,"<10")

Set up similar formulae for the other conditions - you may need to use COUNTIFS if you have more than one condition to trigger the colour (like between two values).

Hope this helps.

Pete

The CF rules are as follows:

equal to and less than 59.9% = red
between 60.0% and 69.9% = yellow
equal to and greater than 70.0% = green

To count red:

=COUNTIF(cell_range,"<0.6")

To count green:

=COUNTIF(cell_range,">=0.7")

To count yellow:

=COUNTIFS(cell_range,">=0.6",cell_range,"<0.7")

Change cell_range as appropriate.

Hope this helps.

Pete