Forum Discussion
Solved
Count By Color with Conditional Formatting
What I'm trying to do is quickly and automatically figure out how many employees I have scheduled for each quarter hour. I am so close to completing this project but I have one issue to overcome. I h...
You may mark it as a "Best solution". Then you'll make me really happy. Haha!
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.