You could try this...
Sub Compare()
Const OutSheet=1
Const IDCol=1
Const FirstRow=1
Dim WB1 as Worksheet, WB2 as Worksheet
Dim FindID As Range
Dim LastRow, LastCol
Dim OutRow, RowLoop, ColLoop
Dim ThisID
Set WB1=Workbooks("MyBook.Xls").Sheets(1)
Set WB2=Workbooks("OtherBook.Xls").Sheets(1) 'Change book and sheet indexes to match your requirements
ThisWorkbook.Sheets(OutSheet).Cells.Clear
OutRow=0
LastRow=WB1.Cells(WB1.Rows.Count,IDCol).End(xlUP).Row
For RowLoop=FirstRow To LastRow
ThisID=WB1.Cells(RowLoop,IDCol).Value
If ThisID<>"" Then
Set FindID=WB2.Columns(IDCol).Find(ThisID, Lookin:=xlValues, LookAt:=xlWhole)
If FindID Is Nothing Then
OutRow=OutRow+1
ThisWorkbook.Sheets(Outsheet).Cells(OutRow,1).Value=ThisID & " - does not exist in both books"
Else
LastCol=WB1.Cells(RowLoop, WB1.Columns.Count).End(xlToLeft).Column
For ColLoop=IDCol+1 To LastCol
If WB1.Cells(RowLoop,ColLoop).Value<>FindID.Offset(0,ColLoop-IDCol).Value Then
OutRow=OutRow+1
ThisWorkbook.Sheets(Outsheet).Cells(OutRow,1).Value=ThisID & " - mismatch in column " & ColLoop
End If
Next ColLoop
End If
End If
Next RowLoop
End Sub
Bookmarks