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

hossboss85

New Member
Joined
Mar 25, 2009
Messages
2
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! :)
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Mar 25, 2009
Messages
2
I knew I was overcomplicating things. :biggrin:

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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,918
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top