Compare two tabs using vlookup

[niebyl2002]

Hello,

I'm trying to optimize my code that compares two different worksheet tabs. Both worksheets have same report, just different days. BTW rows order will be different. I dedicated column A to create unique identifier which will be used for vlookup to find the same row on a new worksheet and then it will compare whole row.

Code needs to go thru around 10,000 rows times 20 columns. If I run a code with that range excel stops responding and quits.

Everything is working if I keep rows below 1000.

Please see my code below and advise what to do?



Sub SheetCompare()



Start = Timer


Sheets("PO Download OLD").Select
Range("A8:BL7000").Interior.ColorIndex = 3

Dim myRow_OLD As Integer
Dim myRow_NEW As
Integer
Dim myCol As Integer
Dim Col1 As
Integer

Col1 = 1

Application.ScreenUpdating = False


For myRow_NEW = 8 To 500
For myRow_OLD = 8 To 500
If Sheets("PO Download NEW").Cells(myRow_NEW, Col1).Value _
= Sheets("PO Download OLD").Cells(myRow_OLD, Col1).Value Then
For myCol = 1 To 64

If Sheets("PO Download NEW").Cells(myRow_NEW, myCol).Value _
= Sheets("PO Download OLD").Cells(myRow_OLD, myCol).Value Then

Sheets("PO Download OLD").Cells(myRow_OLD, myCol).Interior.ColorIndex = 0

End If

Next myCol
End If
Next
myRow_OLD


Next myRow_NEW

Application.ScreenUpdating = True


MsgBox "Report run for " & Timer - Start & " Seconds"


End Sub


Thank you

[TMShucks]

A somewhat non technical approach that may work for you.

Create Helper columns on each sheet. In the Helper column, create a formula to collate the 20 columns. Use the Helper column to match against the other sheet. If it doesn't match, one of the columns is different. Does it matter which one.

You could probably use Conditional Formatting to change the row colour rather than colouring them in first and then removing the colour.

Regards, TMS

[niebyl2002]

Thank you for a quick response but yes it does matter which column. For now my code highlight the whole row but I sort that out once I optimize my code.

I read that arrays can be used for comparing and that they are much faster. What you think guys?

[TMShucks]

You could use the key field to determine if the row exists and then the Helper column/cell to see if the 20 columns match. If they don't, then you loop through the columns to find out which of them are different. That way, you only loop through rows where you know there is a mismatch, not all 10000. Of course, if the majority re mismatches, it might not make a difference.