Count Number of times Color appears in row

Originally Posted by NewYears1978

a) It doesn't update automatically, seems I have to click in that cell, F2 to go in the formula bar and press enter to recount).

That is because changing colours does not trigger a recalculation. As a bare minimum, you need to add Application.Volatile at the start of the function, but that will only make it recalculate when the workbook does- which a colour change will not cause on its own.

b) It doesn't seem to recognize colors set by conditional formatting, which mean sit doesn't work for me. I need it to see conditional formatting not just manual formatting.

You will have to evaluate the conditions in the function, or use a subroutine that can check the DisplayFormat property of the cells (which a UDF cannot). Note that that property is only available from 2010 onwards so would not work for 2003.

Any ideas?

Yes, but probably not one you will like. Don't try to use colours as data. It's not something Excel is good at.

This code should recognize the Conditional Format generated colors:

Please Login or Register  to view this content.

Displayformat does not work in a UDF called from a cell.

Displayformat does not work in a UDF called from a cell.

Hi xlnitwit,

It worked for me

Can you post the workbook? I’d love to see that.

It worked until I tried to save it

Does that make sense??

No, but stranger things have happened in VBA!

Yeah - I can make it work temporarily:

Please Login or Register  to view this content.