Applying conditional formatting to one column based on values in two other columns

[JenW]

Hello,

Question 1:

I need to turn values in column J green if the time listed in that column is equal to or between the times listed in columns H & I.
I need to turn values in column J red if the time listed that column is less than the time listed in column H or greater than the time listed in column I.
I'm trying to apply the conditional formatting to J7:J21 using the respective adjacent H7:H21 and I7:I21 columns/cells; J7's time should be compared to the times in H7 and I7; J8's time should be compared to the times in H8 and I8, etc.

Question 2:

I've used "=F7+TIME(0, 2, 0)" and "=F8-TIME(0, 2, 0)" to generate a time plus or minus two minutes of a specific time listed in column F. The formula changes to generate a time plus or minus five minutes, 15 minutes, etc. farther down in the column; as time progresses the window of time to complete the timed task widens. That's how we're creating the "window" or range of times in columns H & I. Conditional Formatting doesn't seem to like applying rules to time values generated via formulas. Is there a work around for that?

 

[Fluff]

How about

=MEDIAN(H7,I7,J7)=J7

 

[JenW]

How about

=MEDIAN(H7,I7,J7)=J7

Hello and thank you for your suggestion. However, I'm sorry, but I'm confused about how this helps answer either question. Would you please let me know which question you are trying to help me with, and perhaps supply some additional info for clarification? What is the end result this code should generate? How will I know if it's working properly for me? Etc.

 

[Fluff]

That's a conditional formatting formula to turn the cells green.

 

[JenW]

Thank you! Your solution worked perfectly. I added a second rule to turn the times outside of H & I red and the sheet is calculating and formatting exactly as needed.

 

[Fluff]

Glad to help & thanks for the feedback.