Conditional Formatting with Specific Text, relating to another cell value

[Brittropp07]

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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]15[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]18[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD]7[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD]3[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Specification/ Condition:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]< 15[/TD]
[TD]< 40[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]0-10[/TD]
[TD]15-25[/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD]< 10[/TD]
[TD]< 30[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD]< 10[/TD]
[TD]< 20[/TD]
[/TR]
</tbody>[/TABLE]


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

Thank you!

 

[blanchdm]

You'll need to do something like this to specify the upper and lower bounds for each fruit:

[TABLE="class: grid, width: 653"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[TD]Value 1 Lower[/TD]
[TD]Value 1 Upper[/TD]
[TD]Value 2 Lower[/TD]
[TD]Value 2 Upper[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]15[/TD]
[TD]43[/TD]
[TD]0[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]18[/TD]
[TD]24[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD]7[/TD]
[TD]34[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

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]

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
[TABLE="class: cms_table_grid, width: 653"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[TD]Value 1 Lower[/TD]
[TD]Value 1 Upper[/TD]
[TD]Value 2 Lower[/TD]
[TD]Value 2 Upper[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]15[/TD]
[TD]43[/TD]
[TD]0[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]18[/TD]
[TD]24[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD]7[/TD]
[TD]34[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

 

[blanchdm]

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.