Forum Discussion
Count By Color with Conditional Formatting
You may mark it as a "Best solution". Then you'll make me really happy. Haha!
I wonder why you would go so long as to try counting cells based on format using VBA. Perhaps you want to consider a different approach. Enter the following formula in D2 and copy it down and across.
=IFERROR(1/(AND($B2<>"",$C2<>"")/AND(D$1>=$B2,D$1<$C2)),"")
It will test if start and stop times are entered and return a blank for quarters not scheduled and 1 for those that are. In row 17 you can then simply sum the cells above. Add conditional formatting to your liking.
I found an issue with both of our solutions. Example is that if I have an employee scheduled 8 am to 4 pm with each cell being by the quarter hour what is happening is its adding an extra 1 in the cell that's technically the 4 pm to-4:15 pm cell. the cell that is labeled 3:45 pm would technically be 3:45 pm to 4 pm. Any thoughts or suggestions on how to deal with this?
Have a look at the attached workbook. It's the one I used earlier to test your "problem". 8am to 4pm equals to 8 hours, i.e. 32 quarters. In my schedule, 32 columns are coloured, the last one being 3.45pm, marking the beginning of the last quarter. I believe that's correct.
- I see what I did wrong. You're a genius!!! Thank you so much for your help. I really do appreciate it
You may mark it as a "Best solution". Then you'll make me really happy. Haha!