Share via

conditional formatting to highlight values outside the range of two other cells

Anonymous
2016-02-16T23:33:53+00:00

As in the example below, I want to highlight column A values if they are OUTSIDE the range of column B (low value) and column C (high value).

Ie, A2 would not be highlighted because it falls within the range represented by B2:C2

     A3 WOULD be highlighted because it does not fall within the range represented by B3:C3

I know how to apply the conditional formatting to one cell and then autofill the rest of the sheet, but I don't know what formula to enter in Conditional Formatting.  Something like if 'A2 is not greater than/equal to B2 and smaller than/equal to B3'??

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2016-02-17T01:17:46+00:00

    This seems to work:

    =OR(A2<B2,A2>C2)

    10 people found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-02-17T01:11:37+00:00

    Hi Rob,

    Try the CF formula:

             =AND(OR(A2<B2,A2>C2),A2<>"")

    ===

    Regards,

    Norman

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2016-02-17T02:34:00+00:00

    Perfect!

    Thanks Norman

    0 comments
  3. Anonymous
    2016-02-17T02:54:12+00:00

    Hi Rob,

    Perfect!

    You are welcome.

    ===

    Regards,

    Norman

    0 comments