Count Number of Highlighted Cells

[elephant97]

I have a spreadsheet that I go through and highlight different cells based on data requirements. Specifically, I highlight the cells either green, yellow, or red. How can I use the COUNT function (or something else) to count the number of greens, yellows, and reds and return the totals for each color in a different spreadsheet??? Thanks!

 

[boonecr]

NateO, Great routine...works perfectly. I just have one dilemma...can you do something like this that only counts highlighted cells that are visible? I have a table with multiple columns and rows, and I need to filter based on city (for example). When I use your function it counts all cells in the range (table) even though only maybe two rows are visible (rest are filtered out). I tried using variations of SUBTOTAL, but have not been able to solve my problem. Any thoughts?

 

[boonecr]

Well, I figured it out my dilemma shortly after my last post! I simply changed the if/then to an If/and/then and added "cl.rows.hidden = False." so now it reads

If Cell.Interior.Index = ColorIndexNumber And cl.Rows.Hidden = False _

I suppose one could also add a third condition if wanting to count visible columns as well. That would be cl.Columns.Hidden = False

Great Site...so much valuable info here!