Mass conditional formatting

My goal: I am trying to create an automated schedule using conditional formatting that, once a block of time has been assigned, will color code itself. The best and the only way I could figure how to do this is best explained in the screenshot below. 2 conditional formatting formulas so that if either "D" or "P" is input, it will block off a different amount of time.

My problem: I am trying to do this in such a way that I don't have to set conditional formatting for 500+ lines of hours in a week.

Solutions I have tried:

• Copy/paste & Format Painter: I have not been able to figure out a combination of absolute references that will allow me to copy and paste my formatting in such a way that it will work given the parameters above.
• Macros (1): My first pass with macros was a suggestion from this board, (link). Which, once again, I could not figure out how best to utilize it given that absolute references won't work. Seems to just be copying and pasting for me.
• Macros (2): I tried to record myself setting the conditional formatting and using that as a macro, but it will only record the absolute position of the cell that I am applying it to, and I don't know how, if possible, to make it so that the target of the conditional formula shifts downward.

P.S. I'm also looking at possibly using some free appointment scheduling software if I can find one that works for my needs.

Solution 1:

Apply your condition to $B$3:$J$500 and use the following conditional formula for "D":

=COUNTIF(B1:B3,"D")

For "P", you will need to apply a condition to $B$3:$J$3:

=B3="P"

And apply the following formula:

=COUNTIF(B1:B4,"P")

to $B$4:$J$500