Count colored cells by conditional formatting

Hello everyone
I have a UDF that is supposed to count a range of conditional formatting and there is a weird behaviour
The udf works fine in the 'Test' macro but as for the worksheet function usage it gives a VALUE error ( the yellow cell) .. any idea how to fix that?

This thread posted before at this link
http://www.eileenslounge.com/viewtop...nal+formatting

Thanks advanced for any help

Good afternoon YasserKhalil

Your respondent at eileenslonge.com was correct - conditional formatting colours within Excel are not exposed to the VBA object model, so can't be detected.

You will need to determine what conditions change the cell's colour, and test for that condition.
You don't even have to do it in a "one-step" formula - you could use a helper cell with a decision making formula that will read, say, 1 if CF turns cell blue, 2 for red, 3 for green etc and then test for that.

I admit, it's not ideal but it's about the only way if you're going to use CF.

HTH

DominicB

Thanks a lot for reply
What if the code used in standard module not a UDF. Will it be easier to deal with the conditional formatting cells in that case ..? or the issue would still the same to be difficult

That's great. Thanks a lot
So no hope to count or sum cells with CF using UDFs

Thanks a lot anyway for great and awesome help
Best Regards

Thanks a lot for the link
I tried to get how it works but it seems difficult for me. i couldn't apply the solution

Hello again
after I have searched a lot I could find this UDF

Please Login or Register  to view this content.

And for count I used this formula

Please Login or Register  to view this content.

As excel experts, can you please check this UDF and see if it is OK ...?
Hope you can test it and see if there are any errors or are there any possible improvements ..?

Thanks advanced for any kind of help

Please can you test that UDF on several cases of CF ..?

And this is the final version for those who are interested to test this UDF

Please Login or Register  to view this content.