Conditional Formatting based on values from other sheet

[Dmox]

I have two worksheets: Inventory and Status
Status has two columns

A B
Fridge In Stock
Stove Out of Stock
Oven Low Stock

What I would like to do is when sheet2 is modified, "Fridge" for example is
changed from In Stock to Low Stock it searches Sheet1 for the word "Fridge"
and changes the background color to Yellow.

Because "Fridge" can be in any column or row it can't be tied to a specific
cell.

Is there a way to accomplish this through conditional formatting?

 

[Jacob Skaria]

--Select the cells in Status : range ColA and B and name those cells as
myRange..
(Select cells>In the name box (just above cell A1) type myRange and enter)

--In sheet Inventory select the cells
1. Select the cell/Range (say A1:J10). Please note that the cell reference
A1 mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection

2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=VLOOKUP(A1,myRange,2,0)="Low Stock"

4. Click Format Button>Pattern and select your color (say Yellow)
5. Hit OK..this will highlight all cells in sheet Inventory for which the
status is 'Low'



If this post helps click Yes

 

[Dmox]

It's working pretty good, but not quite.

If I type in "Fridge" it works great. But if I type in "White Fridge with
black door" it doesn't work. Is there a way to find "Fridge" in a sentence
or a String?

 

[Dmox]

If I type in "Fridge" it works great. But if I type in "White Fridge with
black door" it doesn't work. Is there a way to find "Fridge" in a sentence
or a String?