that cell will not be counted as being Conditionally Formatted red by the macro. Note that this last macro is not foolproof in that theoretically a cell could be naturally red and Conditionally Formatted as red, both at the same time. Sub CountConditionallyFormattedRedCells()ĬolorCount = ColorCount - ( vbRed) If you only wanted to count the red cells that came from Conditional Formatting, then use this macro instead. MsgBox "Number of red cells in Range " & _ Set Rng = Application.InputBox("Select a range", Type:=8) Here is a macro that will count all red cells in the selected range.ĭim ColorCount As Long, Cell As Range, Rng As Range It is an object and using its Interior.Color property allows you to see the color being displayed in the cell no matter how that color got into the cell. If you are using XL2010 or above, the Range object has a property that has not been advertised all that much which can see CF properties of a cell along with non-CF properties. If you change one and fail to change the other, then you won't get the desired results. Of course, the only caveat to this solution is that you will need to keep the conditions in the macro and the conditions in the conditional formats in sync with each other. This should yield the same count of cells, but is much easier to handle programmatically. The result is that you count cells matching conditions rather than count cells that are colored red as a result of those conditions. Given the difficulty of the task, it may just be easier to recreate the conditions within the macro, and then see which cells meet these conditions. The following page on Chip Pearson's site demonstrates the difficulty in determining conditional colors: There are ways you can work around this with a macro, but it is not for the faint-of-heart. You cannot directly check in a macro what the color of a cell is based on a conditional format. Ronald wants to know if there is a way to count these conditionally red cells, as well. He knows how to create a macro that will examine the cell color and do a count if a cell is formatted directly as red, but the macro won't work with cells that are conditionally formatted. He wants to count the number of cells that are red in the worksheet. The conditions result in the cells being different colors. Ronald has a worksheet that utilizes conditional formatting.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2022
Categories |