Dynamic conditional formatting on activity calendar.

Hello,

I am attempting to use conditional formatting to make a calendar with blocks of different colors for activities.

There are currently formulas in each week's template to bring in the class schedule entered on the [Class List] Sheet. This is accomplished and fine. What isn't, is the logic to make the other, manual, entries, that override the formulas, conditional formatting.

in the screenshot and example, I entered breakfast in cell D4 using the dropdown on the sheet [TEST CALENDAR FOR DELETION]. It overrides the formula and the formatting below works that when breakfast is selected for cell below, the block should be all blue, as it is. The problem is, that logic immediately applies to all blocks for all activities.

How do I make the blocks dynamically update for Wakeup, breakfast, lunch, dinner, exercise, study and bed time as well as keeping the remaining cells below it, not show the dropdown text entered to make it a solid block, with one text line at the top?

All courses are filled in automatically based on the times on the Clast List and thier entire blocks should be grey, like the first line on row 13, column D. Cells D14 through D17 update to the blue, as that is at the top of the heirarchy. Again, the block between 8:30 and 9:15 am changes to blue but should follow the grey formatting. the other activities should do the same, based on the colors in the key in column L. I know it's in the logic hierarchy, but can't quite wrap my mind around it right now. You can see that it is grey orginally if you

CalScreenshot.png


If anyone can make it work, I may have work referals to you in the future. This is the idea of what I am going for with just conditional formatting, minus the lines through blocks. Remember, formulas are in there as a template to pull course schedules. Students then go in and manually update each cell to fill in the other activities. I can't have the text showing for breakfast in each cell they select, but just the 1st in a block. CalScreenshot 2.png

Thank you so much for your help. It will be life-saving!

WillCalScreenshot 3.png


*all sheets are formulated identical. If one blank (formula only) tab is correct, it's an each copy/paste. *this was given to me with about 500 conditional formatting rules. Some were needed, some not. I've got it to here but it has been a struggle for me. I really appreciate the help!

This may do what you want.
1. Top rule: =(ISNUMBER(SEARCH("Course",C4)))*($B4<Cal_Endtime)*(C4<C3)
2. Second rule: =(ISNUMBER(SEARCH("Course",C4)))*($B4<Cal_Endtime)
Let us know if you have any questions.