Comparing two sheets "Run time error '13' :type mismatch error appears near the end of the comparison

Question

I am trying to create a way to automate a comparison and it almost works.I have a macro set to highlight all of the changes between the two sheets and paste them into a third. The problem that I am having is when it is writing the identified cells the macro fails when it has to record a macro that has blank spaces. I get a Run-time error '13': Type mismatch and when I run the debugger it always highlights this line of code

If Not cells.Value = ActiveWorkbook.Worksheets("Previous").cells(cells.Row, cells.Column).Value Then
        cells.Interior.color = vbGreen

I have noticed that I receive the Run-time error '13': Type mismatch when the macro gets to a record that is blank or has blank spaces.

Sub CompareSheets()

Dim cells As range
'Adds a "Changes" sheet
Sheets.Add.Name = "Changes"
'Highlights the changes on the new data
For Each cells In ActiveWorkbook.Worksheets("Current").UsedRange
    If Not cells.Value = ActiveWorkbook.Worksheets("Previous").cells(cells.Row, cells.Column).Value Then
        cells.Interior.color = vbGreen
'Copies the cells with changes and brings them to the "Changes" sheet before highlighting them
        ActiveWorkbook.Worksheets("Changes").cells(cells.Row, cells.Column).Value = cells.Value
        cells.copy
        ActiveWorkbook.Worksheets("Changes").cells(cells.Row, cells.Column).PasteSpecial Paste:=xlPasteFormats
'Copies the rows which have changes in them to the "Changes" sheet
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 1).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 1).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 2).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 2).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 3).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 3).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 4).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 4).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 5).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 5).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 6).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 6).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 7).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 7).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 8).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 8).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 9).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 9).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 10).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 10).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 11).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 11).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 12).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 12).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 13).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 13).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 14).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 14).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 15).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 15).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 16).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 16).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 17).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 17).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 18).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 18).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 19).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 19).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 20).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 20).Value
            ActiveWorkbook.Worksheets("Changes").cells(cells.Row, 21).Value = ActiveWorkbook.Worksheets("Current").cells(cells.Row, 21).Value
    End If
Next

Dim i As Long

'Delete blank rows on "Changes"
    For i = ActiveWorkbook.Worksheets("Changes").UsedRange.Rows.Count To 1 Step -1
        If WorksheetFunction.CountA(ActiveWorkbook.Worksheets("Changes").UsedRange.Rows(i)) = 0 Then
            ActiveWorkbook.Worksheets("Changes").UsedRange.Rows(i).EntireRow.Delete
        End If
    Next i
End Sub

Cyril Cyril · Accepted Answer · 2019-05-20T17:45:04

I attempted to mimic your code and was getting an immediate flag on For Each cells In ActiveWorkbook.Worksheets("Current").UsedRange, so I needed to set the range to work in.

After that, I was getting error 9, subscript out of range, so I swapped your use of Cells to just cell and was able to get this code to work:

Option Explicit

Sub fdsa()
    Dim cell As Range, ur As Range
    Set ur = Sheets(1).UsedRange
    For Each cell In ur
        Debug.Print cell.Value
        If Not cell.Value = Sheets(2).Cells(cell.Row, cell.Column).Value Then
        cell.Interior.Color = vbGreen
        End If
    Next
End Sub

My sheets were simple, but looked like (after the code ran):

Sheets(1), current:

Sheets(2), previous:

Comparing two sheets "Run time error '13' :type mismatch error appears near the end of the comparison

1 Answers