I have a spreadsheet that uses conditional formatting via a drop down list that the cell changes colour depending on result. I want to be able to count the number of cells that are certain colours. I have watch tutorials for Get.cell but that can't work with condition formatting. I've also looked at setting up VBA but can't seem to get that to work either wasn't sure if this is because of the formatting.
Below is VBA to count conditionally formatted colors by leveraging AutoFilter to select by color and DisplayFormat.Interior.Color to get a cell's displayed color created by any means including conditional formats
Function CountColour(ByRef oRange As Range, ByRef oSample As Range) As Long
' This function leverages AutoFilter's ability to select cells by color
' oRange is the cells (arranged in a column) to search ' oSample is a cell that is displaying the color to search for ' Alternatively, we can use lColor as a parameter instead of oSample for those ' situations when we do not have a sample to work with. See below on how to set lColor
Dim lColor As Long
' Displayed color whether from conditional formatting or not lColor = oSample.DisplayFormat.Interior.Color
' Excel applies AutoFilter to the first non-blank cell; thus, if cells above are ' blank, they will remain visible. We don't want that. Since we are counting conditionally ' formatted cells, we should remove non-conditionally formatted cells from oRange Set oRange = oRange.SpecialCells(xlCellTypeAllFormatConditions)