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

On Wed, 17 Sep 2008 10:21:01 -0700, Jim Thomlinson
wrote:

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