Coloured cells based on other cell info

**Nightshift86** · 05-19-2010, 09:32 PM

Another question:

So I have a cell (G1) which I have added a formula to say that when cell J5 equals "base-user", G1 becomes yellow (the background fill)....now I want to make it so that when J5 says "Shared", cell G1 becomes another colour (orange).

In cell J5 i have a drop down so that you can choose either "base-user" or "shared"

The first rule works fine so I naturally used the same procedure for the second rule....I have clicked on the cell i want to format (G1) and then gone through conditional formatting-->new rule-->Use a formula to determine...-->then in the formula space I typed: =$J$5="Shared" and then formatted for backfill colour. I made sure "Shared" was written the same.

It hasn't worked...not sure why???

**NBVC** · 05-19-2010, 10:24 PM

double check that the formula is not surrounded by double quotes.

**Nightshift86** · 05-19-2010, 11:52 PM

yeah i've checked that.....its not.....this really has me stumped! any other ideas?

**Ryan Murtagh** · 05-20-2010, 02:41 AM

When you say drop down, you're talking about a data validation list right? If so, I got it to work for me and attached the result.

If you are using a Forms Combo Box control (drop down), you'll need to set cell J5 as the control's "Cell Link" in the "Format Control" dialog. If you are using an ActiveX Combo Box control, you'll need to set cell J5 as the control's "LinkedCell" in it's "Properties" window.

Hope that helps...

**DonkeyOte** · 05-20-2010, 02:42 AM

Originally Posted by Nightshift86

any other ideas?

Are you sure there are no leading/trailing spaces in the J5 drop down version ?

If you select Shared - and run =J5=TRIM(J5) what is the result ?

If needed, post a sample.