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

[shortysmash]

Hi, I have searched all around but cannot get anything to work. What I want to do is create conditional formatting or VBA (with some help) to fill a cell RED if MAX(I4:R4)>MAX(E4:F4), MIN(I4:R4)<MIN(E4:F4) Else fill cell green.
Im open to any suggestions, solutions. I have a attached a sample of what I would like to do.
Thanks a head of time.

[FDibbins]

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

[popipipo]

Please Login or Register  to view this content.

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)

[Fett2oo5]

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.

[Fett2oo5]

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

[shortysmash]

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

This worked. I guess I was over analyzing it and trying to make it into a more complex if then. I didnt really think about the multiple conditions. Sorry and thanks

[Fett2oo5]

No worries, sometimes it just takes another set of eyes to see it differently.

---

Click *, if the suggestion helps you!
If your problem is solved, then please mark the thread as SOLVED:
Go to above your first post → Thread Tools → Mark your thread as Solved