How to do conditional formatting with time in excel?

Question

I am trying to do conditional formatting with time, in one column I have present time i.e. using =NOW(), in other columns I have a set of time between 0-24 Hrs. All the columns have time in HH:MM::SS format.

When I use conditional formatting on the set of time values such that if present time is between start time and end time, the cells should turn to orange color, if start time and end time was before present time then the cell should be in yellow color, if start time and end time is later than present time the cell should be in green color. Applying conditional formatting works only once but I want the sheet to be dynamic i.e. the cells should change their colors according to real time values.

Please find the following example image

Answer 1

Answer edited, as question clarified:

You could try the following conditional formatting formulae:

Yellow:

=AND($F3<NOW()-TODAY(),$H3<NOW()-TODAY())

Orange:

=AND($F3<NOW()-TODAY(),$H3>NOW()-TODAY())

Green:

=AND($F3>NOW()-TODAY(),$H3>NOW()-TODAY())

Apply each formula to range F3:H6, and format to suit.