Someone like jindon would probably have a fancier way of doing this, but since our match criteria is based on 3 different columns, this was the best I could think of... it is not super fast, but I hope manageable.... see if it works for you:
Sub compare()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lr As Long
Dim i As Long
Dim myMatch As Range
If ThisWorkbook.Worksheets.Count < 3 Then Exit Sub 'Including Index sheet, there must be at least 3 sheets to perform the comparison
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set ws = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count - 1) 'the object "ws" is now the penultimate sheet
Set ws2 = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) 'the object "ws2" is now the last sheet
lr = ws.Range("A" & Rows.Count).End(xlUp).Row
'it is easy to use the find function to find a match for one value, but to match multiple columns without repeated loops
'we will create a helper column that contains the Booking Number, and the Container Number and the Number, so that we can simplify matching
'these helper columns will be deleted afterwards
'here we merge columns K (booking number), AC (container number), and AF (J - Seq Number) into column AZ
ws.Range("AZ2").Formula = "=K2&""^""&AC2&""^""&AF2"
ws.Range("AZ2:AZ" & ws.Range("A" & Rows.Count).End(xlUp).Row).FillDown
ws.Range("AZ2:AZ" & ws.Range("A" & Rows.Count).End(xlUp).Row).Value = ws.Range("AZ2:AZ" & ws.Range("A" & Rows.Count).End(xlUp).Row).Value
ws2.Range("AZ2").Formula = "=K2&""^""&AC2&""^""&AF2"
ws2.Range("AZ2:AZ" & ws2.Range("A" & Rows.Count).End(xlUp).Row).FillDown
ws2.Range("AZ2:AZ" & ws2.Range("A" & Rows.Count).End(xlUp).Row).Value = ws2.Range("AZ2:AZ" & ws2.Range("A" & Rows.Count).End(xlUp).Row).Value
For i = 2 To lr 'to compare all rows in the last sheet
If ws2.Range("AZ" & i).Value <> "^^" Then 'if the column only has ^^ then it was a blank row
Set myMatch = ws.Range("AZ2:AZ" & ws.Range("A" & Rows.Count).End(xlUp).Row).Find(what:=ws2.Range("AZ" & i).Value, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows)
If Not myMatch Is Nothing Then 'if we found a match then we can compare that record
If ws2.Range("A" & i).Value <> ws.Range("A" & myMatch.Row).Value Then ws2.Range("A" & i).Interior.Color = vbYellow
If ws2.Range("B" & i).Value <> ws.Range("B" & myMatch.Row).Value Then ws2.Range("B" & i).Interior.Color = vbYellow
If ws2.Range("C" & i).Value <> ws.Range("C" & myMatch.Row).Value Then ws2.Range("C" & i).Interior.Color = vbYellow
If ws2.Range("D" & i).Value <> ws.Range("D" & myMatch.Row).Value Then ws2.Range("D" & i).Interior.Color = vbYellow
If ws2.Range("I" & i).Value <> ws.Range("I" & myMatch.Row).Value Then ws2.Range("I" & i).Interior.Color = vbYellow
End If
End If
Next i
ws.Columns("AZ").ClearContents
ws2.Columns("AZ").ClearContents
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Bookmarks