Comparing two large sets of data from Sheet1 & Sheet1

[BigMo1987]

Hello, I am trying to compared or tied out two set of data from Sheet1 and 2 but each sheets have over 9000 line items. I already have a VBA codes but due to the size of my data excel crashed. Can someone help? Below is the code.
Sub Compare_Difference()

Dim eRow1&, eRow2&
Dim cell1 As Range, cell2 As Range
Dim rngSht1 As Range, rngSht2 As Range
Dim ws1 As Worksheet, ws2 As Worksheet

Application.ScreenUpdating = False

Set ws1 = ActiveWorkbook.Sheets(1)
Set ws2 = ActiveWorkbook.Sheets(2)

' Get last row for each data on Sheet1 and Sheet2
eRow1 = ws1.Range("A1").End(xlDown).Row
eRow2 = ws2.Range("A1").End(xlDown).Row

' Define Range for each sheet
Set rngSht1 = ws1.Range("A2", "A" & eRow1)
Set rngSht2 = ws2.Range("A2", "A" & eRow2)

' Compare list in ws1 to ws2
ws1.Range("D1") = "Diff (Sht2 - Sht1)"
For Each cell1 In rngSht1
For Each cell2 In rngSht2
If cell2 = cell1 And cell2.Offset(0, 1) = cell1.Offset(0, 1) Then
Select Case True
Case ws2.Range("C" & cell2.Row) = ws1.Range("C" & cell1.Row)
ws2.Range("A" & cell2.Row, "C" & cell2.Row).Interior.ColorIndex = 6
Case Else
ws2.Range("A" & cell2.Row, "B" & cell2.Row).Interior.ColorIndex = 6
ws1.Range("D" & cell1.Row) = ws2.Range("C" & cell2.Row) - ws1.Range("C" & cell1.Row)
End Select
End If
Next
Next

' Compare list in ws2 to ws1
ws2.Range("D1") = "Diff (Sht1 - Sht2)"
For Each cell2 In rngSht2
For Each cell1 In rngSht1
If cell1 = cell2 And cell1.Offset(0, 1) = cell2.Offset(0, 1) Then
Select Case True
Case ws1.Range("C" & cell1.Row) = ws2.Range("C" & cell2.Row)
ws1.Range("A" & cell1.Row, "C" & cell1.Row).Interior.ColorIndex = 42
Case Else
ws1.Range("A" & cell1.Row, "B" & cell1.Row).Interior.ColorIndex = 42
ws2.Range("D" & cell2.Row) = ws1.Range("C" & cell1.Row) - ws2.Range("C" & cell2.Row)
End Select
End If
Next
Next

' Sort ws1
With ws1.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending
.SetRange Range("A1", "D" & eRow1)
.Header = xlYes
.Apply
End With

' Sort ws2
With ws2.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending
.SetRange Range("A1", "D" & eRow2)
.Header = xlYes
.Apply
End With

' Return cursor to cell A1 for each worksheet
Application.Goto ws2.Range("A1"), True
Application.Goto ws1.Range("A1"), True

Application.ScreenUpdating = True

End Sub

 

[Fluff]

Duplicate to: Comparing 2 datasets for reconciliation using VBA

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.