[VBA] Count cells based on color (conditional formatting)

[hossboss85]

Hi all. I've been using this board a ton since starting my internship six months ago. MrExcel and OZGrid have pretty much made me the "Excel Guy" at work, so...many thanks!

I couldn't find a solution to this problem, though.

I have a huge list of hedge funds and I've added several buttons that apply conditional formatting based on, e.g., "fund assets < $100mm," "minimum investment < $250k," etc--the formatting just colors the cell background red.

Conditional formatting is weird in that it doesn't really "color" the cell (the color index is still blank, "-4142"). Even so, I can apply a sort to drop all red-colored cells to the bottom of the list. But when I tried adding code to count the number of NON-red-colored cells (the ones that "passed the test"), there's no way to differentiate between "Pass" and "Fail" funds.

Is there any property of a conditionally-formatted cell that actually *does* change (since color doesn't) that would let me count it separately from the rest of the cells?

Thanks for your help! Much appreciated!

 

[Jonmo1]

Welcome to the board...

Yes, I'm sure it's possible, but It would be MUCH MUCH easier to count the condition that causes the red color...

for example, if cells in column A are colored red IF They = "Yes"

=COUNTIF(A:A,"Yes")

 

[hossboss85]

I knew I was overcomplicating things.

I may just end up doing that, but I do want this functionality, if possible:

Say there are a total of ~3500 funds. I click one of the buttons/macros, like "Firm Assets < $500mm," and that "reddens" 1000 funds. That's easy to count. But if I click another, "Minimum < $250k," that takes out another ~500, *with some overlap* of the "Firm Assets" funds... Without dealing with VBA, I can do a COUNTIFS function, but what if I want to apply only some of the criteria...?

Anyway, I think I may try actually changing cell colors using a loop through each cell instead of applying conditional formatting to a whole range, I'm just worried it will take a long time to run.

Thanks so much for the response, jonmo1! I'll table the issue/thread for now, but I'll revive it if I need more help.