Conditional Formatting with Specific Text, relating to another cell value

Brittropp07

New Member
Joined
Jan 25, 2016
Messages
3
Hello,
I am trying to figure out how I can have conditional formatting depending on text in one cell relating to value in another cell, but for several different items... for example

DATA
ItemValue 1Value 2
Apples1543
Oranges1824
Bananas734
Mango38

<tbody>
</tbody>

Specification/ Condition:
ItemValue 1Value 2
Apples< 15< 40
Oranges0-1015-25
Bananas< 10< 30
Mango< 10< 20

<tbody>
</tbody>


I would like the cells that do not meet specification turn red.

Thank you!
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

blanchdm

New Member
Joined
Jan 27, 2016
Messages
32
You'll need to do something like this to specify the upper and lower bounds for each fruit:

ItemValue 1Value 2Value 1 LowerValue 1 UpperValue 2 LowerValue 2 Upper
Apples1543015040
Oranges18240101525
Bananas734010030
Mango38010020

<tbody>
</tbody>

Then your conditional format equation should look like this:

=OR(B2 < D2,B2 > E2,C2 < F2,C2 > G2)<d2,b2><f2,c2><d2,b2><f2,c2><d2,b2><f2,c2><d2,b2><f2,c2>

(ignore the extra spaces in between the < and > signs; for some reason, a bunch of the formula completely disappeared when I tried to post without spaces)
Note that, by default, conditional format selections have the fixed $ in them. You'll need to delete those (or just manually type the cell references), or this won't work properly.

Finally, select the "Item" column ($A$2:$A$5 in the above example) to be the "applies to" range in the conditional format.</f2,c2></d2,b2></f2,c2></d2,b2></f2,c2></d2,b2></f2,c2></d2,b2>
 

Brittropp07

New Member
Joined
Jan 25, 2016
Messages
3
So there isn't a way that i could have a formula lookup a specific text and then relate to another cell?

For example: IF COLUMN A= "APPLES" COLUMN B<15 AND COLUMN C<40 ?
IF COLUMN A= "ORANGES" COLUMN B 0>AND<10 AND COLUMN C 15>AND<25 ?

A B C
ItemValue 1Value 2Value 1 LowerValue 1 UpperValue 2 LowerValue 2 Upper
Apples1543015040
Oranges18240101525
Bananas734010030
Mango38010020

<tbody>
</tbody>
 

blanchdm

New Member
Joined
Jan 27, 2016
Messages
32
Certainly you could, but you'll need to have a separate note for each individual line item; I was trying to streamline things a little. A couple of questions regarding your desired behavior:

1. Do you want only the word "Apples" to be highlighted, or the whole row?
2. Are the specifications you list considered good or bad? For example, do you want "Apples" to be highlighted when value 2 is below 40, or is that the limit (and you therefore want it highlighted above 40)?

It can definitely be done the way you want, being conditional on the text as well as the values, but the syntax of your second post makes me question which direction to go on the above points.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,519
Messages
5,602,141
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top