Highlight values between

Generic Formula

=AND(cell>=lowervalue,cell<=uppervalue)

AND is a conditional function that returns TRUE when all conditions are met. With the use of Conditional Formatting, you can highlight the cells containing values in a certain range with AND function. To use Conditional Formatting, continue as follow

  1. Select an applied range, for example, B5:G7.
  2. Go to Home, Conditional Formatting, New Rule, Use a formula to Determine which cells to format.
  3. Insert the formula =AND( B5 >= 30, B5 <= 70).
  4. Go to Format, Fill and Choose the highlight color.

Note

The inserted cell must be an “active cell” in the selected range, in this case, B5.

Figure 1. Highlight values between 30 and 70 with AND in Conditional Formatting

Explanation

Conditional Formatting allows the inserted rule to be applied to all cells in the selected range. In the example above, B5 is a relative address to range B5:G7 so the AND function is applied to every cell in B5:G7. The first argument collects the values above 30 and the second argument collects those under 70. AND function combines the arguments to collect the values between 30 and 70; then the Conditional Formatting will highlight the values between them.

Lock in the Ranges

If you want to change the upper and lower limit values without having to go to Conditional Formatting again, you can reference the limits. For example,

=AND(B5>=$E$3,B5<=$G$3)

Now you can freely change the values inside E3 and G3 and the Conditional Formatting rule will be automatically applied.

Another way of doing this is to rename the limit cells E3 and G3. On the Formula Bar, there is a name box where you can set a name to the selected cell and use it without having to set a reference.

Figure 2. Using name box to lock reference

=AND(B5>=lower,B5<=upper)

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar