Conditional Format to compare two worksheets

I received an email today, asking how to compare two worksheets for differences.  Of course, we could easily reach to VBA to do this, but we can also use Conditional Formatting to make these kind of comparisons.  I figured that I'd knock up a quick example, so here goes:

First I set up the workbook so that I'd have two worksheets with similar, but not completely duplicated data:

• On Sheet1, in cells A5:G23, I entered a block of random numbers
• I copied those numbers to Sheet2, cells A5:G23
• I changed some of the numbers on Sheet2, to give me some cells that wouldn't be identical

Now that I was set up to prove it out, I selected Sheet2's A5:G23 range and created a new conditional formatting rule using the following formula:

=A5<>Sheet1!A5

Wouldn't you know it, though, it came back with an irritating error:

Okay, so that's irritating, but not insurmountable by any means.

Next, I set up a named range.

• On Sheet2 select cell A5
• Create a named range with the following formula:
• =Sheet1!A5
• For reference, I created it using the name "rngSht1"

So then, after selecting Sheet2's range of A5:G23, we again head back to the conditional formatting wizard.  This time the rule we use is as follows:

• Criteria  =A5<>rngSht1
• Fill colour  Light red

And now, after applying the format, we end up with a nice display of the cells on Sheet2 that don't match those on Sheet1: