Count Cells in a range with a Certain Color

[AllisterB]

I want to be able to count the number of cells in a Range where the color of the cells is a certain Color. It would be easiest if eth Colour was determined by the Fill color of the Cell in which the Formula sat. The Colour may have been via a manual Fill, Conditional Formatting or from a Style.

I think this would be a VBA solution - so If anyone has something to do the job that would be great.

Thank You

Allister

[NickyC]

This gets a bit messy because the usual way to count colours (interior.color) does not work for conditional formatting, and while displayformat works in macros for some odd reason it doesn't in user-defined functions. this workaround uses evaluate and seems to work
The user-defined formula ColCount counts all the cells in the selected range that have the same interior colour as the active cell. So entering
=ColCount(G1:G20)
in cell M5 of your sheet returns the value 9

Please Login or Register  to view this content.

[AllisterB]

Works nicely Nicky. To get it to update I needed to do a f2 on the cell with the formula in it. Is this what you expected. How do I get it to update automatically?
Allister

[NickyC]

Hi Allister
sorry, I overlooked you post
Updating user-defined functions can be a bit tricky. This site suggests some workarounds: