Count By Color with Conditional Formatting

Sheriffg84

Hi, I haven't tried to use VBA to count conditional formatting before so thank you for this challenge. 

The issue here is that you are always counting the number of cells in column D (from the original conditional formatting) because the ConvertFormula function resizes the row but not the column. My solution is to add a column resize parameter to your final ConvertFormula statement as follows:

dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1, CFCELL.Column - 3))

Please remember that this works because your conditional formula is based on column D. So, when you calculate column D the resize parameter is 4-3=1, calculate column E is 5-3=2 and so on. Therefore, if you have other spreadsheets with different conditional formatting rules, you may need to change the "-3" in this calculation. 🙂

Hope this helps.