0

I've 3 columns in a spreadsheet and I want to highlight the values in column value based on the bounds specified in columns min and max.

enter image description here

If the number in column value lies within the range [min, max], the cell has to be highlighted green. If the number is less than min or greater than max the cell has to be highlighted red. And if there is no value in value, the cell has to be colored yellow.

Example,

enter image description here

Suggestions on how to define the rules for conditional formatting in excel will be really helpful.

1
  • =and(a2>=b2,a1<=c2) assuming your data starts in A1 that should help you to the first one
    – PeterH
    Dec 14 '20 at 16:59
1

You can do this with 3 conditional formatting rules based on formulas. Highlight your values and then create those rules based on these formulas:

First Formula (fill is yellow)

=isblank(a2)

Second Formula (fill is green)

=and(a2>=b2,a2<=c2)

Third Formula (fill is red)

=OR(A2<B2,A2>C2)
    
    

Then go into [Conditional Formatting... Manage Rules] and order them as such:

enter image description here

It's imporant to have the yellow one first, and then select the "Stop if True" box. You don't need this for the others.

Final Output:

enter image description here

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.