Counting Colored Cells from a Conditional Formatting statement

Question

So I'm revisiting this from yesterday:

Multi-column vlookup conditional formatting

Everything is working as intended with the conditional formatting statement that Scott Holtzman provided (Thanks Scott!). Now I've run into a little issue. I need to count the individual cells based on their background color and have it show in another sheet. I found this:

https://www.ablebits.com/office-addins-blog/2013/12/12/count-sort-by-color-excel/

The VBA script is doing well to count cells that I manually fill... but it's not counting the cells that are filled by the conditional formatting function. Anyone have any idea as to how to get around this little hiccup?? As always, anyone who can provide any insight is always appreciated!! :)

As you've discovered, conditional formatting is not considered the same as formatting for anything apart from visiual display. If you want to count these cells, you'll need to do something like have another column which checks to see if the original conditional format conditions are met, and then show as '1', and then count all those 1's. There will be other ways, but all would involve formulas that look at the values in the original cells, not the formatting of those cells. — Grade 'Eh' Bacon
So I would have to set an if statement within the sheet to produce a '1' if the vlookup from the conditional formatting were true and then count the number of 1's then? for instance, if(vlookup function, 1, "") ?? — mdaube
How about you go a step back and count based on the conditions that give those cells colors in the first place? — Code Different
You can use DisplayFormat to determine the colour shown in a cell but: 1. you need at least Excel 2010; and 2. You can't use it in a UDF, so you'd need a macro or event code. — Rory
If you use the second method that Scott showed, the one with the words in column I,J and K. Then you could just use two countif() statements on those cells. — Scott Craner

Naren Neelamegam Naren Neelamegam · Accepted Answer · 2015-10-15T18:03:45

Unfortunately, there is not a direct way / VBA methods or properties can give the color of the cell which has conditional formatting applied. As you know, your default/manually filled color will be overridden by conditional formatting. When it comes to conditional formatting, a cell can have more than one condition applied which means more than one color is possible for the cell, which is very dynamic.

cColor= rng.FormatConditions(1).Interior.ColorIndex ' Color of formula 1 if true
cColor= rng.FormatConditions(2).Interior.ColorIndex ' Color of formula 2 if true
cColor= rng.FormatConditions(3).Interior.ColorIndex ' Color of formula 3 if true

Also, these format condition objects have priority value set, so they can get overridden over other based on priority. You can run through all conditions applied on a cell and find colors for each formula,

    For i = 1 To rng.FormatConditions.Count
        cColor = rng.FormatConditions(i).Interior.ColorIndex ' Color of formula i
    Next i

But, this gives only the colors assigned to each condition, how to get the current color of a cell on which these conditions are applied. You will have to evaluate the condition manually with the cell value to conclude whether the condition for the cell returns true or false, then get the color for the same.

Seems to be difficult, isn't it? But, this is better explained with code which may help you get what you want. Please refer to the links below,

Get Displayed Cell Color (whether from Conditional Formatting or not)

Conditional Formatting Colors

Hope that helps.

Counting Colored Cells from a Conditional Formatting statement

1 Answers