How to fix Error Runtime 9 when comparing two workbooks

Question

I am trying to create a formula that compares two workbooks.

I get:

Run-time error 9.

Here is the code:

Sub Compare()

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

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

Helps to tell us which line is highlighted when you get the error, and what the error message is (not just the number) — Tim Williams, May 27 '22 at 17:04
Thanks Tim... it is Run-Time error '9': Subscript out of Range. Also I'm not sure which line the Sub() Compare line is yellow — Deories McLendon, May 27 '22 at 17:06
This is not likely your issue, but please be aware that your `Range` and `Cells` references are not specifying which workbook or sheet. See [this answer](https://stackoverflow.com/a/10717999/4717755) for more information. — PeterT, May 27 '22 at 17:50
Do any of your cells contain error values? It's good practice to declare all of the varaibles you use before you use them (putting `Option Explicit` at the top of every module will help you do that - https://www.wallstreetmojo.com/vba-option-explicit) — Tim Williams, May 27 '22 at 17:54

score 0 · Answer 1 · answered May 28 '22 at 00:02

Compare the Same Cells in Two Different Workbooks

This is just a basic example that may serve you well at this stage.

Option Explicit

Sub CompareBasic()
    
    ' Source: compare; just read
    Dim swb As Workbook: Set swb = Workbooks("Testing1.xlsx")
    Dim sws As Worksheet: Set sws = swb.Worksheets("Sheet1")
    
    ' Destination: compare and highlight
    Dim dwb As Workbook: Set dwb = Workbooks("Testing2.xlsx")
    Dim dws As Worksheet: Set dws = dwb.Worksheets("Sheet1")
    Dim dlRow As Long: dlRow = dws.Cells(dws.Rows.Count, "A").End(xlUp).Row
    Dim dlCol As Long
    dlCol = dws.Cells(1, dws.Columns.Count).End(xlToLeft).Column
    
    Application.ScreenUpdating = False
    
    Dim r As Long
    Dim c As Long
    
    For r = 2 To dlRow
        For c = 2 To dlCol
            If dws.Cells(r, c).Value <> sws.Cells(r, c).Value Then
                ' Using 'Color' is preferred for it to work the same,
                ' not depending on which color palette is used.
                dws.Cells(r, c).Interior.Color = vbYellow
            Else
                ' It may have previously been different (highlighted)
                ' but now it's the same (not highlighted):
                dws.Cells(r, c).Interior.Color = xlNone
            End If
        Next c
    Next r

    Application.ScreenUpdating = True
    
    MsgBox "Differences highlighted.", vbInformation

End Sub

How to fix Error Runtime 9 when comparing two workbooks

1 Answers1

Compare the Same Cells in Two Different Workbooks