"Subscript Out of Range" error occurs when the below Macro is run to
compare excel sheets are on different workbooks. This error doesn't
occur when the sheets are in the same workbook. Can someone help me
fix this.. Thanks in advance.
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim i As Integer, j As Integer
Dim rc1 As Integer, rc2 As Integer, cc1 As Integer, cc2 As Integer
Dim maxR As Integer, maxC As Integer, cv1 As String, cv2 As String
With ws1.UsedRange
rc1 = .Rows.Count
cc1 = .Columns.Count
End With
With ws2.UsedRange
rc2 = .Rows.Count
cc2 = .Columns.Count
End With
maxR = rc1
maxC = cc1
If maxR < rc2 Then maxR = rc2
If maxC < cc2 Then maxC = cc2
For j = 1 To maxC
For i = 1 To maxR
cv1 = ws1.Cells(i, j).Value
cv2 = ws2.Cells(i, j).Value
If cv1 <> cv2 Then
ws1.Cells(i, j).Interior.ColorIndex = 3
ws2.Cells(i, j).Interior.ColorIndex = 3
End If
Next i
Next j
End Sub
Sub TestCompareWorksheets()
' compare two different worksheets in the active workbook
'CompareWorksheets Worksheets("Export Worksheet"), Worksheets
("Sheet1")
' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Export Worksheet"), _
Workbooks("ACD.xls").Worksheets("Export Worksheet") ' Error
Occurs here
End Sub