VLOOKUP to change cell color?

**killar65** · 08-14-2010, 02:07 AM

Hey guys,

I am wanting to change a cell color on one sheet based on a value contained in a cell on another sheet after looking up a players name...does that make sense?? Check out the attached spreadsheet.

The cell i want to change color is D6 on the panel sheet.

I need it to look at the players name at J2, then I guess vlookup that name on the next sheet...if that player has a value in the Y column (either a 1, 2 or 3) then the cell color at D6 will change to red.

Any help would be greatly appreciatted.

Thanks,

James

**royUK** · 08-14-2010, 02:34 AM

You could use Conditional Formatting with this formula as the condition

=OR(VLOOKUP(J2,'adelaide 08'!C1:AA35,23,0)+$A$6=3,VLOOKUP(J2,'adelaide 08'!C1:AA35,23,0)+$A$6=2,VLOOKUP(J2,'adelaide 08'!C1:AA35,23,0)+$A$6=1)

**killar65** · 08-14-2010, 12:34 PM

Roy,
Many thanks for your prompt reply to my Q...pasted your equation in, hit ok...got the following error message..."you cannot use references to other worksheets or workbooks for conditional formatting criteria"...seems pretty sucky.

James

**Marcol** · 08-14-2010, 01:09 PM

Try in D6

Please Login or Register  to view this content.

Then Conditional formatting

Cell value > Betwen

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Applies to

Please Login or Register  to view this content.

Format both text and fill Red.

Format D2 font = White to hide any value other than 1 to 3

Hope this helps

**killar65** · 08-14-2010, 09:46 PM

Marcol,

Awesome. Worked a treat. Many thanks to you and royUK for your assistance in this matter. Take care.

**royUK** · 08-15-2010, 02:48 AM

It works for me

**royUK** · 08-15-2010, 12:30 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**ZEINABM** · 03-01-2017, 04:42 PM

1.JPG
Hi
I want to change a column color on one sheet based on a value contained in a cell on another sheet after looking up a animals name.
I have 2 sheet... first sheet have several column and first column is name... in second sheet, I have several column and one of them is important (position) for me that characters are dead or alive...
2.JPG
Foe example, i want to change color A2 on sheet1 to red color when that is dead in next sheet. I attached my excel.
I hope, you understand my aim.

Thanks a lot,