Thread: Conditional Formatting Across Worksheets

I've had a search through the forum and can't quite find what I'm after, so apologies if this has been answered before and I simply can't find it!

In one Worksheet, I have a list of contacts, all of which have a unique (alphanumeric) reference codes in column A.

In another worksheet, I have a list of these reference codes for contacts interested in a product (imported from another source).

I'm trying to get Excel to conditionally format the rows in the first Worksheet (where the contact details are) depending on whether the reference number exists in the exists in the imported data in the second worksheet. The second worksheet also has the reference codes in column A.

I can't get the above to work at all...

Here's the kicker though...I need it to work in a template spreadsheet I have. So, I have a spreadsheet empty of data, import the contacts into one worksheet, then eventually import the interested contacts into the other worksheet, thus the conditional formatting needs to work on the entire column ($A:$A) and not a specific range. The number of contacts and interested contacts can vary greatly...anything from a couple of hundred to a good few thousand.

Any help much appreciated.

Oh, and I'm using Excel 2004 (Mac)...