Match Cells expanded

[Vic]

I hate to post similar question, but...
How can I highlight the entire row in Sheet1 when A1 matches A:A of Sheet2?
Thank you.

 

[Luke M]

Select row 1 (or just part of row 1). Format-Conditional Format.
Formula is:
=ISNUMBER(MATCH($A1,'Sheet2'!$A:$A,0))

Copy formatting across/down as needed.

 

[Vic]

I inseterted the following formula
=ISNUMBER(MATCH($A1,'DSMB'!$F:$F,0))

and got a message:
You may not use references to other worksheets or workbooks for Conditional
Formating criteria.

I am using excel 2003. What can be done?
Thanks

 

[Max]

You can use INDIRECT to get the CF to work across sheets:
=ISNUMBER(MATCH($A1,INDIRECT("'Sheet2'!A:A"),0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Luke M]

On your DSMB sheet, select column A. Now go to Insert - Name - Define. Give
this range a suitable name (MyRange).

Now, in your conditional format formula on sheet 1, change formula to:
=ISNUMBER(MATCH($A1,MyRange,0))

Voila! You're now referencing an outside worksheet. Take that XL!

 

[Max]

Although it probably didn't appeal to you for some reason,
the earlier method using INDIRECT does work fine
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---