David McRitchie posted this message June 11,2006

You may not use references to other worksheets or Workbooks for Conditional
Formatting criteria.

To get around this obstacle you might use a named range to refer to a range on
another worksheet.

The restriction also means that you cannot use a formula referencing your
personal.xls in a user defined function.

You can get around that by creating a reference in your VBE from your workbook
to your personal.xls

For more information on Conditional Formatting:
http://www.mvps.org/dmcritchie/excel/condfmt.htm

For more information on named range
http://www.mvps.org/dmcritchie/excel...htm#namedrange


Gord Dibben MS Excel MVP

On Sat, 29 Jul 2006 18:34:29 -0400, Excelenator
wrote:


Conditional formatting wont allow you to reference another worksheet,
however, as a work around you could add a column and paste in the
formula

=NOT(ISERROR(VLOOKUP(A1,Sheet2!A1:A7,1,FALSE)))

Where cell A1 contains the employee number and Sheet2!A1:A7 contains
the employee numbers on the other sheet. This formula will return TRUE
if the employee is on the other sheet and FALSE if it is not. You can
then hide this column if you want and use the result in your
conditional formatting by highlighting the range you want to
conditionally format and for the first record say you put the formula
above in column D you would select Formula and type in =$D1 and select
the red font format.