计数彩色单元格不适用于Excel中的条件格式 [英] Counting coloured cells doesn't work with conditional formatting in Excel

问题描述

尝试使用这个VBA代码来计算工作表上某些彩色单元格的数量：

Trying to count the number of certain coloured cells on my worksheet using this peice of VBA code :

Function CountRed(MyRange)
CountRed = 0
For Each Cell In MyRange
If Cell.Interior.Color = RGB(255, 0, 0) Then
CountRed = CountRed + 1
End If
Next Cell
End Function

基本上，细胞。现在，如果我自己对它们进行着色，那么这个工作正常，但是如果我把条件格式化放在我的工作表中来对这些单元进行着色，那么它不起作用。

Basically, counts the number of red cells. Now this works fine if I colour them myself but if i put conditional formatting in my worksheet to colour these cells it doesnt work.

这是我的条件：

=AND(NOT(ISBLANK(A3)),ISBLANK(D3))

基本上如果第一个单元格是填充的，而不是这个单元格，那么颜色就会变红。

Basically if the first cell is populated and this one is not then colour it red.

现在，当我使用我的'CountRed'函数来查看这个单元格（具有条件格式的单元格）是否为红色时，它不起作用，但是当我覆盖它时，即填写它红色我自己并运行该功能再次工作。

Now when I use my 'CountRed' function to see if this cell (the one with the conditional formatting) is red it doesnt work, but when I 'overwrite' it, i.e fill it Red myself and run the function again it works.

我以为也许这是与在CountRed函数之前调用的条件格式，但是我没有有任何运气：（

I thought maybe it's to do with the conditional formatting being called before the CountRed function but I didn't have any luck :(

推荐答案

这是因为条件格式化不是Interior.color集合的一部分，因此无法读取或根据具有条件格式的单元格中的基础值是什么，您可以计算文字值。

That's because conditional formatting is not part of the Interior.color collection and as such cannot be read or counted. Depending on what the underlying value is in the cell with the conditional formatting you can count the literal values instead.

例如，将单元格值设置为红色，绿色，黄色，但设置条件格式设置背景和前景颜色相同（即红色为红色，绿色为绿色等）这样您可以计算一些数据，使用您要解释的值来获取条件colo r作为列计数。

For example, set the cell values to Red, Green, Yellow but set the conditional formatting to set the background and foreground colors the same. (i.e. red on red, green on green, etc.) This way you have something to count. Use the values you are interpreting to get to the conditional color as the column to count by.

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