trying to have cell color fill based on multiple values

[C-Rad]

Greetings, I am trying to have a cell in a worksheet fill a certain color based on the value of a cell in another worksheet but still in the same workbook.

Example: If cell A1 (sheet1) equals 1 then cell B1 (sheet2) is green, but if cell A1 (sheet1) equals 2 then cell B1 (sheet2) is yellow and if cell A1 (sheet1) equals 3 then cell B1 (sheet2) is Red

I have tried the conditional formating yet that seems to only work based on one value unless I am missing something.

I certainly hope this is possible?? Thank you for taking the time reading!

 

[SteveO59L]

Hello and welcome to the board

Conditional Formatting is the way to do this

What version of Excel are you using?

 

[C-Rad]

Thanks for the quick reply back Steve. I am using Excel 2007

 

[SteveO59L]

You will need to define Sheet1!A1 as a named range, you can then used this name in the conditional formatting "Use a formula to determine which cells to format.

So

if you define the name of sheet1!a1 as sao1.

In conditional formatting

=sao1=1, format as green

next rule

sao1=2, format as yellow

etc

 

[C-Rad]

Just a little clarification on the "You will need to define Sheet1!A1 as a named range" How do I give it a name range? The sheets do have specific names to them.

The sheet that needs the formating is called Store Check Q2.11 and the sheet that has the actual numbered value is called Action Results.

 

[SteveO59L]

Select cell sheet1!a1.

On the Formulas tab, there should be (about midway) the "Defined Names" section. Select the Define Name option. This then give cell sheet1!a1 a specific name , which can be subsequently used in your conditional formatting formula.

If you cant get this from my explanation (cos' I'm not v.good at explaining) send me a pm with your e-mail, and I'll send you a worked example

 

[C-Rad]

Steve, I was not able to get it. I figured out how to define it, but yet, for some reason, the format still wouldn't work. I shot you a PM, hope to get an email back. Thanks

 

[C-Rad]

Ok... after trying a few more, I am coming to the realization that its my conditional formatting now. When I type in a value of 1 in the cell, I get yellow, if I type 2 I get green, and when I type 3 I also get green in the cell of my other worksheet. I am not able to match up and get 1= green, 2=yellow, 3=red. Any suggestions? The rule type I am using is "Cells that Only Contain:" and applying a rule for each color.

Suggestions?