Comparing Two Workbooks Error

[GoPirates]

I am trying to create a formula that compares two workbooks. I cannot figure out why I keep getting error code: Run-time error 9. Can someone assist please?
Here is the code:


Sub Compare()
'Just named subroutine
Dim wb1 As Workbook
Dim wb2 As Workbook


Set wb1 = Workbooks("Testing1.xlsx")
Set wb2 = Workbooks("Testing2.xlsx")
'Setting variable to represent last row and last column
lRow = Cells(Rows.Count, 1).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
'Variable to represent looping each row and each column
For i = 2 To lRow
For j = 2 To lCol
'Created the IF then Statement to Highlight Cells that show a difference
If wb2.Sheets("Sheet1").Cells(i, j) <> wb1.Sheets("Sheet1").Cells(i, j) Then
wb2.Sheets("Sheet1").Cells(i, j).Interior.ColorIndex = 5


End If
Next j
Next i


End Sub

[Paul_Hossler]

Works for me

It does help if you tell which line generated the error

[snb]

Both files have to be loaded.
Preferably both ranges should be the same size.


Sub M_snb()
sn = getobject("G:\OF\testing1.xlsx").sheets(1).usedrange
sp = getobject("G:\OF\testing2.xlsx").sheets(1).usedrange

for j= 2 to ubound(sn)
for jj =2 to ubound(sn,2)
if j<= ubound(sp) and jj <= Ubound(sp,2) then If sn(j,jj) <> sp(j,jj) Then workbooks("testing2.xlsx").sheets(1).cells(j,jj).Interior.ColorIndex = 5
next
next
End Sub

[Paul_Hossler]

1. I assumed that both were open in my test. I should know better :banghead:

2. In my test I added logic to use the latest row and the largest column

3. I would think that .UsedRange would be unreliable since it returns whatever has been used until its reset. Maybe .CurrentRegion would be better