Change cell background colour depending of other cell value

[OlivierS]

Hi,

I have a tab (let say Sheet1) where I have a list of customers and their health status.
In another tab (Sheet 2), I want to change the background colour of my customer, depending on their health value.

What I've done so far, is to get the health value from my Sheet 1 and display it in my Sheet 2, using a formula: =LOOKUP(A12,Sheet1!G:G,Sheet1!H:H)

The value can be Green, Yellow or Red.

I've tried to use the Conditional Formatting in order to display my customer background colour in my Sheet 2, but I'm failing ...
I've tried the 'Classic' way, with either:
=IF($AA$13="Red",AA13)
or

=$AA$13="Red"

No chance.

Any tip?

Thank you

 

[etaf]

what version of excel are you using

what do you type into the cell for the health value - or do you just fill a cell with colour
or are the words actually typed Green, Yellow or Red.

you should be able to do a lookup
=VLOOKUP(A1,Sheet1!$A$1:$B$4,2,FALSE)="Red"

and repeat for yellow and green

see attached link
https://www.dropbox.com/s/urz17rhrku8f0z9/cond_frmt_colour_on_sheet2.xlsx

for 2007 or 2010 excel version
Conditional Formatting


Highlight applicable range >>
A1:A1000 - if this is where the customer are you want filled


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=VLOOKUP(A1,Sheet1!$A$1:$B$4,2,FALSE)="Red"


Format… [Number, Font, Border, Fill] - fill RED
choose the format you would like to apply when the condition is true
OK >> OK

Now repeat for the next two formulas

=VLOOKUP(A1,Sheet1!$A$1:$B$4,2,FALSE)="yellow"
=VLOOKUP(A1,Sheet1!$A$1:$B$4,2,FALSE)="green"

 

[OlivierS]

Brilliant!
Works like a charm.

Thank you Wayne.