从条件格式化语句计数有色单元格 [英] Counting Colored Cells from a Conditional Formatting statement

问题描述

所以我从昨天开始重新审视：

So I'm revisiting this from yesterday:

多列vlookup条件格式化

一切都按照Scott Holtzman提供的条件格式化语句（谢谢Scott！）工作。现在我遇到一个小问题。我需要根据自己的背景颜色对单个单元格进行计数，并将其显示在另一张表中。我发现这个：

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/

VBA脚本对于手动填充的单元格进行计数很好，但不计算由条件格式化函数填充的单元格。任何人有什么想法如何摆脱这个小打嗝？一如往常，任何可以提供任何洞察力的人总是感激！

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

推荐答案

不幸的是，没有直接的方法/ VBA方法或属性可以赋予具有条件的单元格的颜色格式化应用。如您所知，您的默认/手动填充颜色将被条件格式化覆盖。当涉及条件格式化时，单元格可以使用多个条件，这意味着单元格可以有多种颜色，这是非常动态的。

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

但是，这只给出分配给每个条件的颜色，如何获取应用这些条件的单元格的当前颜色。您将必须使用单元格值手动评估该条件，以得出该单元格的条件是否返回true或false，然后获取相同的颜色。

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,

获取显示的单元格颜色（不论是否从条件格式化）

条件格式颜色

希望有帮助。

这篇关于从条件格式化语句计数有色单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！