Conditional Formatting based on values from other sheet

D

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?
 
J

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
 
D

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?
 
D

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top