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:

sub macro_1()
dim count
for count = 6 to 3000 
  if Range("I"& count) = "Red" then
   Range("A" & count & ":I" & count).interior.colorindex=3
  elseif Range("I" &count) = "Green"
   Range("A" & count & ":I" & count).interior.colorindex=4
  elseif Range("I & count = "Yellow"
   Range("A" & count & ":I" & count).interior.colorindex=27
  else
    Range("A" & count & ":I" & count).interior.colorindex=xlnone
end if
next
end sub

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:

sub macro_1()
dim count
on error go to found_value
for count = 6 to 3000 
  if Range("I"& count) = "Red" then
   Range("A" & count & ":I" & count).interior.colorindex=3
  elseif Range("I" &count) = "Green"
   Range("A" & count & ":I" & count).interior.colorindex=4
  elseif Range("I & count = "Yellow"
   Range("A" & count & ":I" & count).interior.colorindex=27
  else
    Range("A" & count & ":I" & count).interior.colorindex=xlnone
end if
next
on error goto 0
exit sub
found_value:
Range("A" & count & ":I" & count).interior.colorindex=Range("A" & count-1).interior.colorindex
end sub