Well my friend, it is not pretty, but you can can set conditional formatting to the following function for Cell AD12 and paste the formatting over to the others.
Pretty much the Code checks what Day of the week it is, and depending on the day of the week it counts blank cells two days to the left (omitting weekends), a day before and a day after, and two days after. If the blank count is high enough, it highlights it whatever color you set
=IF(AD$10="M",IF(OR(COUNTBLANK(Z12:AD12)=5,COUNTBLANK(AA12:AE12)=5,COUNTBLANK(AD12:AF12)=3),1,0),IF(AD$10="Tu",IF(OR(COUNTBLANK(Z12:AD12)=5,COUNTBLANK(AC12:AE12)=3,COUNTBLANK(AD12:AF12)=3),2,0),IF(AD$10="W",IF(OR(COUNTBLANK(AB12:AD12)=3,COUNTBLANK(AC12:AE12)=3,COUNTBLANK(AD12:AF12)=3),3,0),IF(AD$10="Th",IF(OR(COUNTBLANK(AB12:AD12)=3,COUNTBLANK(AC12:AE12)=3,COUNTBLANK(AD12:AH12)=5),4,0),IF(AD$10="F",IF(OR(COUNTBLANK(AB12:AD12)=3,COUNTBLANK(AC12:AG12)=5,COUNTBLANK(AD12:AH12)=5),5,0))))))
Bookmarks