Format Cell is Sheet1.A1 = Sheet2.A1

Basically I have two sheets of data, which I have sorted to ensure that they are in the same order, but I need to highlight differences between the two.

I have already used a 3rd sheet to do a basic '=Sheet1!A1=Sheet2!A1 to give me a list of true and false, but I then have to keep flicking between the sheets to find out which cells are different and what they say.

What I want is a macro which I can run which compares the cells and then formats them (say colour them red) if the cells have different values. As it is a matrix from A1:BC1494 I am getting stuck trying to nest loops to make it check each cell.

Any help appreciated!

Thanks in advance

Use your formula in Conditional Formatting applied to the cells on either sheet1 or sheet2. Use the Formula Is option for the CF.

I tried that, but it says you can't use references to other worksheets in the CF formula.

Yes, you are right. you cannot refer to other worksheets with conditional formatting

Well, not directly and I should have remembered.
Create a named range for the list of cells on sheet2 then use this named range as the reference in a COUNTIF formula for the CF.

Example:
Sheet2 A2:Axxx = "the list" and is given the name Sht2Data

On sheet1, using the CF Formula option

=COUNTIF(Sht2Data,A2)>0