Higlight duplicates on 2 sheets w conditional formatting

[jimstrongy]

I have 2 sheets abc and xyz and in both of them in the range of A5:A20 has data. I would like a formula for conditional formating to higlight items on sheet abc that are duplicates on both sheets. And same for sheet xyz to highlight duplicates that are on both sheets

Thank you,

[ChemistB]

Select your data on abc, conditional formatting> new rule > use formula

=ISNUMBER(MATCH(A5, xyz!$A$5:$A$20,0))
format as desired
Repeat for xyz
Did that help?

[Peter.M]

You cannot use conditional formatting across different worksheets but there is a nifty workaround using named ranges.

Highlight A5:A20 in sheet abc and name that range "abc_range". Do the same for xyz.

Then use the following formula for your conditional formatting in sheet xyz:

Please Login or Register  to view this content.

[ChemistB]

In Excel 2010, which is what jim says he has, you can use CF across spreadsheets.

[jimstrongy]

In Excel 2010, which is what jim says he has, you can use CF across spreadsheets.

Thank you ChemistB