Counting Colored Cells from a Conditional Formatting statement

vba excel conditional-formatting countif
mdaube picture mdaube · Oct 15, 2015 · Viewed 14.1k times · Source

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

Answer

Naren Neelamegam picture Naren Neelamegam · Oct 15, 2015

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.

Related questions

Conditional Formatting using Excel VBA code

I have Range object called DistinationRange which contains reference to range B3:H63 I want to apply the following two conditional formatting rules using Excel VBA code dynamically. (Because the range would not be same all the time) If Cell …

Read More
Conditionally formatting cells if their value equals any value of another column

I have data in the A and B columns. B column's data is mostly duplicates of A's data, but not always. For example: A Budapest Prague Paris Bukarest Moscow Rome New York B Budapest Prague Los Angeles Bukarest I need …

Read More
Using VBA to apply conditional formatting to a range of cells

I would like to know how to access the column in conditional formatting titled 'Applies To' and input my own conditions. I have included a screenshot for better reference. My code for adding the syntax in conditional formatting is, With …

Read More