Linking data between sheets to change color of cell?

[mkinney]

I am trying to create a simple sync between two sheets. We have township
range section names and I want to enter a simple Y or N on one sheet for the
work being completed there. Then I want that Y or N answer to change the
color of the associated cell in the next sheet. Is there an easy way to do
this? I tried searching this discussion board but there are so many various
topics and my 'tech' lingo is not quite what it used to be.

Thanks for your help ahead of time.

Mike

 

[Jim Thomlinson]

You can do this via conditonal formatting. The issue that you have is that
conditional formats can not look at values on other sheets as their
criteria... To that end here is a work around.

Assuming that your Y's and N's are in column A of Sheet1 and you want the
colour coding on column B of sheet2.

Add a formula to B1 on sheet 2 something to the effect of...
=Sheet1!a1
to bring over the Y or N
Now with B1 sheet 2 selected select Format -> Conditional Format...
In the Wizard that pops up add the criteria Cell Value is Equal and ="Y"
Chang ethe format to change the color of both the text and the pattern to
some colour such as Green.

The entire cell should now appear green if Shee1 A1 is Y

 

[Gord Dibben]

CF can use a cell from another sheet if you give the cell a defined name.

Not very practical if many cells would require names but can be done.

Sheet1 A1 is named hoohah

On sheet 2 in CF enter =hoohah=Y

Format to a color and OK


Gord Dibben MS Excel MVP