donna

You can use references to other sheets or workbooks if you use a defined name.

From Chip Pearson's site...............

"Using Defined Names In Conditional Formatting

As noted above, custom functions in Conditional Formatting cannot reference
cells in other worksheets in the same workbook, and cannot reference cells in
other workbooks. However, you can get around this limitation by using
defined names. Create a defined name which refers to the list in the other
workbook or worksheet, and then use that name in your custom function.

For example, suppose you want to make cell A1 on Sheet1 red if that cell's
entry is not found on a list on Sheet2, cells B1:B10. If you tried to use the
formula =COUNTIF(Sheet2!$B$1:$B$10,A1)=0 as your formula, you would receive an
error message from Conditional Formatting. To get around this error, create a
defined name called MyList which refers to the range =Sheet2!$B$1:$B$10 and
use the name in your custom formula:

=COUNTIF(MyList,A1)=0

URL to site....... http://www.cpearson.com/excel/cformatting.htm

Gord Dibben Excel MVP

On Wed, 15 Dec 2004 13:47:06 -0800, donnaK
wrote:

I have written an IF function where the true value is a Lookup function. I
would like either the cell background or the text to format in Red if the
Lookup portion of the IF function is utilized (this would be the value if
true portion). I cannot use Conditional Formatting as my function refers to
different workbooks. Correct?