Need Help. Conditional formatting a cell if between two values or not between

do both criteria have to be met, or just 1 of them?

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =OR(MAX(I4:R4)>MAX(E4:F4), MIN(I4:R4)<MIN(E4:F4)) if either is met
or =and(MAX(I4:R4)>MAX(E4:F4), MIN(I4:R4)<MIN(E4:F4)) if both need to be met

=median(i4;e4;f4)=i4

You can also use this short formula
If the result is 'true' then 'green' else 'red'.

Sorry, this wil only work with one value (i4)not with a range (I4:R4)

Check out the conditional formatting on cells I17 → M17

Conditional Formatting has a check for "Cell Value Is"
"between" _____ and _____
and
"not between" _____ and _____

I just selected the cells that are labeled Specification / Limits

Is that what you are looking for?


Edit: Note I put $ on the column but not the row number that way you can stretch the cells down to the range you want, if you are so inclined.

Sorry I forgot to set a no color as default see this example for the cell to be blank if no value is entered.

Check out the conditional formatting on cells I17 → M17

Cell Value is equal to 0 = blank
Cell Value is between $E17 and $F17 = Green
Cell Value is not between $E17 and $F17 = Red