Excel Macro to Compare Two Excel Sheets Cell by Cell

VBA & macros · Updated November 21, 2026

To compare two worksheets in Excel for Microsoft 365 on Windows, use a VBA macro that checks the calculated value in each cell and highlights differences. The example below compares worksheets named Sheet1 and Sheet2 at the same cell addresses; change those names in the code to match your workbook.

What the macro compares

This macro checks cells by position: A1 on the first sheet against A1 on the second, B1 against B1, and so on. It compares each cell’s calculated value, not its formula text or formatting. A formula such as =SUM(B2:B5) is therefore compared by the result it currently displays. If two cells show the same result but use different formulas, this macro treats them as a match.

It checks from row 1 and column A through the furthest row and column containing a value or formula on either sheet. Differences are highlighted with a pale red fill on both worksheets, and a message reports the number of mismatched cells. A cell containing a value on one sheet and an empty cell at the same address on the other counts as a difference. A truly empty cell and a formula that returns an empty string are treated as equivalent.

This is a good fit when the two sheets have the same layout and records are meant to stay in the same rows. It is not a record-matching tool: if one sheet has been sorted or has an inserted row, many cells may differ even when the underlying records are present on both sheets.

VBA macro to compare two sheets

In the code, replace Sheet1 and Sheet2 with the exact worksheet tab names. The comparison is case-insensitive for worksheet names, but spaces and other characters must match. The macro looks in the workbook that contains the code.

Option Explicit

Sub CompareTwoSheets()
    Dim wsA As Worksheet, wsB As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim r As Long, c As Long
    Dim differences As Long
    Dim cellA As Range, cellB As Range

    ' Change these names to match the worksheet tabs.
    On Error Resume Next
    Set wsA = ThisWorkbook.Worksheets("Sheet1")
    Set wsB = ThisWorkbook.Worksheets("Sheet2")
    On Error GoTo 0

    If wsA Is Nothing Or wsB Is Nothing Then
        MsgBox "Check that the workbook contains worksheets named Sheet1 and Sheet2."
        Exit Sub
    End If

    lastRow = Application.Max(LastContentRow(wsA), LastContentRow(wsB))
    lastCol = Application.Max(LastContentColumn(wsA), LastContentColumn(wsB))

    If lastRow = 0 Or lastCol = 0 Then
        MsgBox "Neither worksheet contains values or formulas to compare."
        Exit Sub
    End If

    For r = 1 To lastRow
        For c = 1 To lastCol
            Set cellA = wsA.Cells(r, c)
            Set cellB = wsB.Cells(r, c)

            If Not SameCellValue(cellA, cellB) Then
                cellA.Interior.Color = RGB(255, 199, 206)
                cellB.Interior.Color = RGB(255, 199, 206)
                differences = differences + 1
            End If
        Next c
    Next r

    MsgBox differences & " differing cell(s) found."
End Sub

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

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

Private Function SameCellValue(ByVal cellA As Range, _
                               ByVal cellB As Range) As Boolean
    Dim valueA As Variant, valueB As Variant
    valueA = cellA.Value2
    valueB = cellB.Value2

    ' Compare errors by their displayed error text.
    If IsError(valueA) Or IsError(valueB) Then
        If IsError(valueA) And IsError(valueB) Then
            SameCellValue = (cellA.Text = cellB.Text)
        End If
        Exit Function
    End If

    ' Treat an empty cell and a formula returning "" as equivalent.
    If IsEmpty(valueA) Then valueA = vbNullString
    If IsEmpty(valueB) Then valueB = vbNullString

    If VarType(valueA) <> VarType(valueB) Then Exit Function
    If VarType(valueA) = vbString Then
        SameCellValue = (StrComp(valueA, valueB, vbBinaryCompare) = 0)
    Else
        SameCellValue = (valueA = valueB)
    End If
End Function

How to add and run the macro

  1. Save a copy of the workbook before running the macro. It changes cell fills, and Excel macros cannot be undone with Ctrl+Z.
  2. Save the file as an Excel Macro-Enabled Workbook (.xlsm) using File > Save As. A standard .xlsx file does not retain VBA code.
  3. On Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, use Developer > Visual Basic; the keyboard shortcut can depend on the keyboard and function-key settings.
  4. In the editor, choose Insert > Module, paste the full code into the module, and edit the worksheet names if needed.
  5. Return to Excel and run CompareTwoSheets from Developer > Macros. On Windows, Alt+F8 opens the Macro dialog. On a Mac, use Developer > Macros.

If Excel displays a security warning, only enable macros when you trust the workbook and its source. Macro settings vary by organization, and a workplace policy may prevent running VBA.

How the comparison works

The helper functions use Excel’s Find method to locate the last row and column containing a value or formula. They search for formulas as well as constants, so a formula returning an empty-looking result still helps define the comparison area. Formatting alone does not extend that area. The code then checks every address in the rectangular area from A1 to the furthest used row and column on either sheet.

Each comparison reads the cell’s Value2. This means the macro compares stored results rather than number formats: a date and a number with the same underlying serial value can match even if they look different. Text comparisons are case-sensitive, so North and north count as different. Numeric values are compared directly, without a tolerance for small rounding differences.

Excel error values are checked separately so an error does not cause a normal value comparison to fail. When both cells contain errors, the code compares their displayed error text. The final count is a count of individual cells, not rows or records; if three cells differ in one row, the message counts three.

Choose the right comparison method

Before relying on a result, decide what “same” means for your task. The macro is useful for exact, address-by-address checks, but different goals call for different approaches:

  • Same layout, check calculated results: Use this macro. It quickly marks value mismatches at matching addresses.
  • Check whether formulas changed: Compare formula text instead of Value2. This code will not flag different formulas if their results happen to match.
  • Match records after sorting: Compare using a unique ID or key column, not row position. Otherwise a reordered list can produce many misleading highlights.
  • Compare appearance: Inspect number formats, fonts, fills, or other formatting separately. This macro only changes fill color to mark differences; it does not compare existing formatting.
  • Review a formal or large-scale workbook comparison: Consider Excel’s comparison tools or a controlled Power Query workflow, depending on your Excel edition and whether you need cell-level, formula-level, or record-level results.

Common problems and limitations

Sheet name not found: The example expects tabs named Sheet1 and Sheet2. If your tabs are called January and February, change both names in the Worksheets lines. The code uses ThisWorkbook, meaning the workbook containing the macro; putting the code in a different workbook changes which workbook it searches.

Old highlights remain: The macro colors mismatches red but does not remove fills from cells that matched on a previous run. If you correct a difference and run it again, that cell may still look highlighted. Review results against the latest run, or clear the old fills manually in the comparison area before running again. Clearing fills can also remove intentional formatting, so do it only on a copy or where appropriate.

Comparison takes longer than expected: The code scans every cell in the rectangle from A1 to the furthest content row and column, including blank cells inside that rectangle. A stray value far to the right or near the bottom can make the scan much larger than the visible table. Remove unintended content and check the actual data extent if performance is poor.

Rows do not line up: A row inserted in one sheet shifts the position-based comparisons below it. Sort both sheets consistently or use a key-based comparison when records can move. The macro does not infer that a record on row 20 corresponds to a record on row 23.

Values look equal but are flagged: Look for differences in text capitalization, extra spaces, numeric values stored as text, or tiny numeric precision differences. The macro does not trim spaces or apply a numerical tolerance. Decide whether those distinctions matter before changing the comparison rules.

Safety and practical checks

Run the macro on a backup first, particularly if the workbook has carefully formatted cells. The only workbook changes made by this code are the highlighted cell fills, but the operation is not reversible through Excel’s normal undo history. It does not delete data or change formulas.

For a more reliable review, compare sheets with the same headers and layout, check the reported mismatch count, and inspect several highlighted addresses on both tabs. If the sheets contain sensitive information, use a trusted local workbook and follow your organization’s rules for macros. A macro-enabled file can contain executable code, so do not enable macros in files from an unknown source merely to view their data.

Alternatives to a VBA macro

For a small table, a formula in a third worksheet can compare corresponding cells, and conditional formatting can make differences visible without running VBA. That approach is easier to inspect but must be filled across the relevant range and adjusted for the workbook’s layout. For a key-based comparison, lookup formulas can identify whether IDs exist on both sheets, while Power Query can load, merge, and compare larger tables when the data has consistent columns.

Excel’s Inquire add-in includes a workbook comparison feature in editions where that add-in is available; availability depends on the Microsoft 365 plan and installation. It is designed for broader workbook analysis rather than this small, customizable cell-highlighting task. If the exact comparison method matters, confirm whether you need values, formulas, formats, or records matched by ID before choosing a tool.

Frequently Asked Questions

Can this macro compare sheets with different numbers of rows?
Yes. It scans through the furthest content row and column found on either sheet. Cells beyond the shorter sheet’s data are treated as empty, so a populated cell opposite an empty one is highlighted as a difference.

Does the macro compare formulas or displayed values?
It compares calculated values through Value2. Two different formulas with the same result are treated as equal. To detect formula changes, the comparison logic must be adapted to inspect each cell’s Formula property instead.

Can I compare sheets when their rows are in a different order?
Not reliably with this macro. It compares the same cell address on both sheets, not records by identity. Use a unique key such as an account number or product ID to match records when their order can change.

Why are some highlighted cells still red after I fix them?
The example adds a fill to differences but intentionally does not erase existing fills. This prevents it from automatically removing formatting you may have applied for another reason. Clear the previous comparison fills carefully before a new run, ideally on a backup.

Will the macro work in Excel for the web?
No. Excel for the web can open macro-enabled workbooks, but it cannot create, edit, or run VBA macros. Open the workbook in the desktop Excel application for Windows or Mac to run this code.

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.