0

Suppose I have a simple dataset in Excel:

Column 1    Column 2
A           1
B           1
C           2
D           4
E           5
F           9

P1

Now I want to mark the whole row with green color, if the value in column 2 is larger than 3. I apply a conditional format with the formula =$B2>3 applied on range =$A$1:$B$7 and it does not work:

ex1

One line where the value is 2 is marked green and one where it is 9 is not marked.

Now I want to mark the row, however only, if the value in column 2 is between 3 and 6. I apply the formula =AND(3<$B2;$B2<6) to the same range and it does not work:

ex2

Nothing is marked green.

Where is my mistake?

Update: I now also tried =AND(3<$B1;$B1<6), but still nothing is marked green?

Improve this question
5
  • 2
    For your 1st problem. You apply the wrong formula to the wrong range. Use =$B1>3 or use a different range, e.g. =$A$2:$B$7 Commented May 18, 2022 at 8:57
  • 1
    The 2nd problem. Same as 1st, apply to a different range or use =AND($B1>=3,$B1<=6) Commented May 18, 2022 at 8:59
  • 1
    Oh! I saw comment after posting answer. Commented May 18, 2022 at 9:00
  • Thanks, but the second one still does not work. Commented May 18, 2022 at 9:01
  • 1
    Why =AND(3<$B1;$B1<6) instead of =AND($B1>=3,$B1<=6)? Commented May 18, 2022 at 9:08

1 Answer 1

Reset to default
1

Change =$B2>3 to =$B1>3 which should work for you.

Your formula start range and apply start range must be same. Otherwise CF will highlight different cells.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

For the second problem I now entered =AND(3<$B1;$B1<6) and it still does not work? Nothing is marked green.
The problem seems to be somehow connected to the AND condition. If I juse use a single condition it works, but not with AND?
@PSt As JvdV mentioned =AND($B1>=3,$B1<=6) this should work for you.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.