If match is larger than a figure...

Closed
meishapie - Aug 12, 2010 at 05:00 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 18, 2010 at 07:09 AM
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!

Related:

2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 17, 2010 at 10:52 PM
You can use conditional format. You would need two conditions as

=AND(A1=1, B1>2000)

and

=AND(A1=2, B1>3000)
0
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?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 18, 2010 at 05:26 AM
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 18, 2010 at 07:09 AM
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))
0