Conditional formatting on adjacent cell

Dear all,

I have data as attached.
I wish to do the following. All time in after 9:01 is highlighted in red, before 09:00 is green.
For cells with 00:00 it should be in yellow.
However the highlighted area is not only the cells but the adjacent of it as shown.

If weekends could be avoided for formatting i.e. not in any colour that would be better.


Thank you for your help

In your example workbook, you could use this formula in cell I6:
=IF(A7="",I5,IF(AND(NOT(WEEKDAY(A7)=1),NOT(WEEKDAY(A7)=7)),IF(B10>TIME(9,0,0),"Red",IF(B10=TIME(0,0,0),"Yellow","Green")),"Blank"))
then autofill down the column and that will get you the colour each row should be. Then either set up the conditional formatting or put a macro in along the lines of:

Please Login or Register  to view this content.

Thanks dude. That in fact helps!!
Have a nice day

I have a small matter... It will have the value of "#VALUE" in I36 and I37. Which the macro will break since the value is not in the list.
How to get rid of that?

Untested but maybe:

Please Login or Register  to view this content.