VBA Macro to Compare Two Excel Sheets

VBA & macros · Updated November 21, 2026

A VBA macro can compare two Excel worksheets cell by cell and list mismatches on a separate report sheet. The example below compares the values at matching cell addresses, so A1 is checked against A1, B1 against B1, and so on; edit the worksheet names in the code before running it.

What this comparison checks

Comparing two sheets can mean several different things. This macro performs a positional comparison: it checks the value in each cell on one sheet against the value in the same address on the other. It finds differences in content, including cells that contain a value on only one sheet. It does not rearrange records or try to match rows by a customer number, product code, or other key.

The macro reads each cell’s Value2. It therefore compares the result stored in a cell, not the formula used to calculate that result. For example, if both sheets show 25 in C4, the cells match even if one contains =10+15 and the other contains the number 25. Formatting differences, comments, hyperlinks, data validation, and other cell properties are not checked.

This distinction matters when choosing a method. Use a positional comparison when the sheets have the same layout. If rows may be in a different order, first match records using a unique identifier, or use a tool that supports key-based comparison.

VBA macro to compare two sheets

The macro below checks the used areas of worksheets named Sheet1 and Sheet2. It creates or clears a worksheet named Compare Results, then adds one report row for each mismatch. It leaves the two source sheets unchanged.

Option Explicit

Sub CompareTwoSheets()
    Dim wsA As Worksheet, wsB As Worksheet, report As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim r As Long, c As Long, outRow As Long, diffCount As Long
    Dim a As Variant, b As Variant

    ' Change these names to match the two worksheets in this workbook.
    Set wsA = ThisWorkbook.Worksheets("Sheet1")
    Set wsB = ThisWorkbook.Worksheets("Sheet2")

    ' Reuse the report sheet, or create it if it does not exist.
    On Error Resume Next
    Set report = ThisWorkbook.Worksheets("Compare Results")
    On Error GoTo 0
    If report Is Nothing Then
        Set report = ThisWorkbook.Worksheets.Add( _
            After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        report.Name = "Compare Results"
    Else
        report.Cells.Clear
    End If

    report.Range("A1:E1").Value = Array("Sheet 1", "Sheet 2", _
        "Cell", "Value in Sheet 1", "Value in Sheet 2")
    outRow = 2

    lastRow = Application.Max(LastUsedRow(wsA), LastUsedRow(wsB))
    lastCol = Application.Max(LastUsedColumn(wsA), LastUsedColumn(wsB))

    For r = 1 To lastRow
        For c = 1 To lastCol
            a = wsA.Cells(r, c).Value2
            b = wsB.Cells(r, c).Value2
            If Not SameValue(a, b) Then
                report.Cells(outRow, 1).Value = wsA.Name
                report.Cells(outRow, 2).Value = wsB.Name
                report.Cells(outRow, 3).Value = wsA.Cells(r, c).Address(False, False)
                report.Cells(outRow, 4).Value = a
                report.Cells(outRow, 5).Value = b
                outRow = outRow + 1
                diffCount = diffCount + 1
            End If
        Next c
    Next r

    report.Columns("A:E").AutoFit
    MsgBox diffCount & " difference(s) found. See the Compare Results sheet.", _
        vbInformation
End Sub

Private Function LastUsedRow(ByVal ws As Worksheet) As Long
    Dim found As Range
    Set found = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False)
    If Not found Is Nothing Then LastUsedRow = found.Row
End Function

Private Function LastUsedColumn(ByVal ws As Worksheet) As Long
    Dim found As Range
    Set found = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, MatchCase:=False)
    If Not found Is Nothing Then LastUsedColumn = found.Column
End Function

Private Function SameValue(ByVal a As Variant, ByVal b As Variant) As Boolean
    ' Flag error values for review rather than comparing error codes.
    If IsError(a) Or IsError(b) Then Exit Function

    ' Treat a truly empty cell as different from a formula returning an empty string.
    If IsEmpty(a) Or IsEmpty(b) Then
        SameValue = (IsEmpty(a) And IsEmpty(b))
        Exit Function
    End If

    If VarType(a) <> VarType(b) Then Exit Function
    If VarType(a) = vbString Then
        SameValue = (StrComp(a, b, vbBinaryCompare) = 0)
    Else
        SameValue = (a = b)
    End If
End Function

In the code, the less-than and greater-than operators in the final function are written as <> because this is HTML. In the VBA editor, enter the actual VBA operator <> as two characters: less-than followed by greater-than. If copying from a rendered page converts those characters to HTML entities, replace the entity with the operator in the editor.

How to add and run the macro

  1. Save a copy of the workbook before running the macro. Macros cannot be undone with Ctrl+Z, and a copy gives you a straightforward way to recover from an unexpected result.
  2. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, then paste the code into the module.
  3. Change Sheet1 and Sheet2 in the code to the exact tab names you want to compare. Keep the quotation marks. Worksheet names are case-insensitive in Excel, but spaces and spelling must still match.
  4. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the macro in that file.
  5. Return to Excel and press Alt+F8, select CompareTwoSheets, and choose Run. Review the Compare Results tab.

On a Mac, the Visual Basic Editor is available through Tools > Macro > Visual Basic Editor; the keyboard shortcut may require Option+F11 or Fn+Option+F11, depending on the keyboard. The VBA editor and macro features are not available in Excel for the web, so open the workbook in the desktop app to add or run this code. Ribbon and dialog wording can differ slightly between Windows and Mac.

How the report handles differences

The macro finds the last nonempty or formula-containing row and column on each source sheet, then compares the rectangular area extending to the larger of those boundaries. This means that a value in a cell that is blank on the other sheet is reported. Cells outside both used areas are not needlessly scanned.

Each report row gives the two sheet names, the address, and the values from each sheet. For example, if the source sheets have different values in D7, the report identifies D7 and places each value in its own column. If you later change either source sheet, run the macro again to refresh the report; the previous report contents are cleared first.

Text comparisons are case-sensitive: North and north are treated as different. A genuinely empty cell is also treated as different from a formula that returns an empty string. Error values are flagged for review rather than compared by error type, so even matching errors such as the same displayed error on both sheets may appear as differences. These choices make the report explicit, but they may need adjusting for a particular workbook.

Important assumptions and limitations

  • Matching positions: the macro compares addresses, not records. If a row was inserted on one sheet, many later cells may be reported even when the underlying records are otherwise the same.
  • Values rather than formulas: formulas that return the same result count as a match. If formula text matters, compare each cell’s Formula property instead of Value2.
  • Underlying values rather than appearance: a number stored as 0.5 and a date serial displayed as a date may be treated as the same underlying number. Number formats are not compared.
  • Report sheet name: the macro uses Compare Results for its output. If that name is already used by a source sheet, change the report name in the code to a separate name.
  • Workbook reference: ThisWorkbook means the workbook containing the macro. Store the macro in the workbook being compared. If the code is stored elsewhere, its workbook reference must be adjusted carefully.

Common problems and safe use

If Excel reports “Subscript out of range,” check that both worksheet names in the code match existing tabs in the workbook that contains the macro. If macros are blocked, use only a workbook and macro source you trust. On Windows, macro options are under File > Options > Trust Center > Trust Center Settings > Macro Settings; avoid enabling all macros globally just to run one file.

This code clears the contents and formatting of the Compare Results sheet each time it runs, but it does not edit the two named source sheets. Do not put important notes or unrelated data on the report tab. A macro can still have unintended effects if it is changed, pasted incorrectly, or pointed at the wrong workbook, which is why saving a separate copy first is prudent.

Large worksheets can take time because the macro checks each cell in the combined rectangular area, including blank cells between used cells. A sheet with data scattered far apart can therefore require more comparisons than its visible data count suggests. For a very large workbook, limit the comparison to the relevant rows and columns or consider a purpose-built comparison tool.

Choosing between VBA and other methods

For a small, one-time check, formulas, conditional formatting, or a manual review may be easier to maintain than a macro. VBA is useful when you repeat the same comparison and want a consistent, address-based mismatch report. It is also customizable: the report can be extended to include row numbers, formulas, or a chosen range.

If the worksheets contain the same records in different orders, a positional macro is the wrong comparison by itself. A better workflow is to match rows using a stable identifier, then compare selected columns for each matched record. Power Query can help shape and merge tables using key columns; for direct workbook comparison, Microsoft’s Spreadsheet Inquire add-in may be available in some Microsoft 365 or Office editions, but availability depends on the installed edition and setup.

Frequently Asked Questions

Can this macro compare sheets with different numbers of rows?
Yes. It checks through the maximum used row and column across both sheets. A populated cell with no counterpart at the same address is reported as a difference.

Does it compare formulas or displayed results?
It compares calculated values returned by Value2. Two cells with the same result match even if their formulas differ. To compare formulas, change the values read by the macro to each cell’s Formula property.

Can it highlight differences on the original worksheets?
Yes, but the example deliberately writes a separate report instead. Highlighting source cells requires changing the code and deciding how to handle old formatting. Keep a backup before adding that behavior.

Why does the macro report cells that look identical?
They may differ in case, contain an empty string from a formula rather than a truly blank cell, or hold error values. They can also look alike because of number formatting while storing different underlying values. Check the cell contents and the comparison rules before treating a reported mismatch as a data problem.

Can I run VBA in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, edit, or run VBA macros. Use the Excel desktop application for those tasks.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.