Count conditionally formatted cells in excel

[katiejessop]

hi

I have a report that conditionally formats for in stock (green) and out of stock (red) cells.

I would like these to automatically add up, as at the moment I am filtering by colour for each store and for each colour. CountifRed doesn't work because its not classed as red.
Please help

thank you

[Solus Rankin]

Can you post your workbook as an example?

[katiejessop]

Hi

Unfortunately even the most shrunk down version of the work book it says the file is too large

[Izandol]

You must count using the same conditions as the formatting instead of counting the formats. For example if your conditional format puts red colour on cells greater than 10 you must count cells greater than 10 instead of counting red cells.

[Solus Rankin]

Or you can format the cells with code instead of excel native conditional formatting.

[ragulduy]

Please Login or Register  to view this content.

[Izandol]

@yudlugar
With Excel 2010 you may use cl.DisplayFormat to return the formatted properties of a cell. It will fail if called from a cell as UDF but will work in a subroutine as you have written.

[ragulduy]

@Izandol

That would certainly be simpler! It's not there in my 2003 version though.

[Izandol]

It only became available with 2010.