Previous Topic Next Topic

If match is larger than a figure...

Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
Hello,

I have a list of data that looks something like this:

A B
1 2356
2 2596
2 3569
1 5096

and I have a condition for which "1" should not be more than 2000 and "2" should not be more than 3000.

Is there a way to use conditional formatting such that it can lookup the data in column A and if the data in column A is >2000, or if the data in column B is > 3000, then shade the cell red?

If I can't use conditional formatting what other ways can I use?

Appreciate all help!

2 replies

Answer 1 / 2
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
You can use conditional format. You would need two conditions as

=AND(A1=1, B1>2000)

and

=AND(A1=2, B1>3000)
Answer 2 / 2

Thanks for the help! I have one follow-up question:

I understand that conditional formatting only allows 3 conditions but if I have 4 conditions to test, i.e.

A B
1 2356
2 2596
2 3569
1 5096
3 9239
4 22200

and my conditions are "1" should not be more than 2000 and "2" should not be more than 3000, "3" should not be more than 4000 and "4" should not be more than 5000

and all I want is if the data doesn't meet the conditions, the cell should turn red...is there any way to do it without using VBA?
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
For more than 3 conditions then you would need to use a macro

https://ccm.net/forum/affich-21716-conditional-formatting-more-than-3-in-excel#1
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
Well come to think of, since you are only interested in one color, you dont need two conditions

You need one only and that will work for 4 too

=OR(AND(A1=1, B1>2000) , AND(A1=2, B1>3000), AND(A1=3, B1>4000), AND(A1=4, B1>5000))
Ask a question