vba 在excel中计算条件格式的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21047816/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
counting conditionally formatted cells in excel
提问by Manatee_Outlaw
Thank you very much for looking into my issue.
非常感谢您调查我的问题。
Basically I've got a dashboard that I have conditionally formatted - depending on what value is returned to that cell will be if the cell turns red or remains white/unshaded. I have a series of rows at the top of the dashboard that need to total the number of red cells under each column (so H8 sums the total # of red cells in the range H10:H21).
基本上我有一个我有条件格式化的仪表板 - 取决于单元格变成红色或保持白色/无阴影时返回到该单元格的值。我在仪表板顶部有一系列行，需要计算每列下红色单元格的总数（因此 H8 将 H10:H21 范围内的红色单元格总数相加）。
I have attempted to write a VB Macro to count the cells. The function is called with =CountRed(Range:Range), so in my previous example cell H8 =CountRed(H11:H21).
我试图编写一个 VB 宏来计算单元格。该函数是用 调用的=CountRed(Range:Range)，所以在我之前的示例单元格 H8 中=CountRed(H11:H21)。
The problem I have is that the macro does not return the correct numberof red cells. (It will return 5 when there are 3, for example.) I also have no idea why my function isn't dynamic; Using both Application.Volatileand Application.Volatile(True)doesn't change a thing.
我的问题是宏没有返回正确数量的红细胞。（例如，当有 3 个时，它将返回 5。）我也不知道为什么我的函数不是动态的；使用两者Application.Volatile并Application.Volatile(True)不会改变任何事情。
Function CountRed(MyRange As Range)
Dim iCount As Integer
Application.Volatile
iCount = 0
For Each cell In MyRange
If cell.Interior.ColorIndex = 22 Then
iCount = iCount + 1
End If
Next cell
CountRed = iCount
End Function
All formatted cells are the same shade of red (22).
所有格式化的单元格都是相同的红色阴影 (22)。
Thank you again for your help!
再次感谢你的帮助！
回答by Joel Cox
You probably want to look at the countif function.
您可能想查看 countif 函数。
http://office.microsoft.com/en-au/excel-help/countif-HP005209029.aspx
http://office.microsoft.com/en-au/excel-help/countif-HP005209029.aspx
You may need to reproduce the conditional formatting logic into the if condition of this function, but that should be pretty straight forward. Post some more information about the sheet & conditional formatting rules if you'd like help with that.
您可能需要将条件格式逻辑复制到此函数的 if 条件中，但这应该非常简单。如果您需要帮助，请发布有关工作表和条件格式规则的更多信息。
回答by Dmitry Pavliv
Try to use
尝试使用
If cell.DisplayFormat.Interior.ColorIndex = 22 Then
instead
反而
If cell.Interior.ColorIndex = 22 Then
Note, that accordingly to this articleDisplayFormatproperty does not work in user defined functions if you call them directly from the sheet. But if you will call it from macro, it works:
请注意，如果您直接从工作表中调用用户定义的函数，则相应于本文的DisplayFormat属性在用户定义的函数中不起作用。但是如果你从宏调用它，它的工作原理：
Sub test()
MsgBox CountRed(Range("C1:C12"))
End Sub
回答by Sherwood Botsford
The CELL function may be the answer you need.
CELL 函数可能就是您需要的答案。
One of the info_type's is format. While it doesn't cover all types of formatting, it covers a broad enough set so that it may suit your need.
info_type 之一是格式。虽然它没有涵盖所有类型的格式，但它涵盖了足够广泛的集合，因此它可能适合您的需要。
It cann pick up whether it's formatted for currency, or is colored, or if has parentheses. It can pick up left, right, centered, filled text.
它无法识别它是针对货币进行格式化的，还是带有颜色的，或者是否有括号。它可以拾取左、右、居中、填充的文本。
It cannot pick up background color, font type/weight/slant.
它无法拾取背景颜色、字体类型/粗细/倾斜。
