All geek questions in one place

Counting color cells from a conditional formatting instruction

So, I am reviewing this from yesterday:

Conditional formatting with multiple vlookup columns

Everything works as intended with the conditional formatting statement provided by Scott Holtzman (thanks to Scott!). Now I am facing a small problem. I need to count individual cells based on their background color and show it on another sheet. I found this:

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

The VBA script does its best to count the cells that I manually populate ... but not counting the cells that populate the conditional formatting function. Anyone have an idea how to get around this little hiccup? As always, anyone who can provide any insight is always appreciated !! :)

+4
source share
1 answer

Unfortunately, there are no direct path / VBA methods / properties that can give the color of a cell that has conditional formatting. As you know, the default / manual color will be overridden by conditional formatting. When it comes to conditional formatting, a cell can have more than one condition, which means that a cell can have more than one color, 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

In addition, these format state objects have a predefined priority value, so they can be redefined over others depending on the priority. You can fulfill all the conditions applicable to the 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

, , , . , , true false, .

, , ? , , . . ,

( )

, .

+1

Source: https://habr.com/ru/post/1611812/

More articles:


All Articles