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
- Save a copy of the workbook before running the macro. It changes cell fills, and Excel macros cannot be undone with Ctrl+Z.
- Save the file as an Excel Macro-Enabled Workbook (
.xlsm) usingFile > Save As. A standard.xlsxfile does not retain VBA code. - On Windows, press
Alt+F11to open the Visual Basic Editor. On a Mac, useDeveloper > Visual Basic; the keyboard shortcut can depend on the keyboard and function-key settings. - In the editor, choose
Insert > Module, paste the full code into the module, and edit the worksheet names if needed. - Return to Excel and run
CompareTwoSheetsfromDeveloper > Macros. On Windows,Alt+F8opens the Macro dialog. On a Mac, useDeveloper > 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
- Import a CSV into an Existing Excel Workbook
- How to Compare Two Excel Sheets and Find Every Change
- How to Compare Two Excel Sheets
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Cross Product: Create Cartesian PairsExcel has no built-in worksheet function named CROSSPRODUCT for pairing two lists, but you can create a VBA…
- Microsoft Excel VBA: What It Is and How to Use ItMicrosoft Excel VBA is the programming language built into the desktop versions of Excel that lets you automate…
- Excel VBA PasteSpecial: Values, Formats, and MoreIn Excel VBA, use Range.PasteSpecial after copying a source range to paste only selected parts, such as values,…
- Excel VBA: Check Whether a String Is Empty or BlankIn Excel VBA, use Len(text) = 0 to check whether a string has no characters. If you also…
- Excel VBA vs Power Query: Key Differences and When to Use EachUse Power Query when the main job is importing, cleaning, combining, and reshaping data that you will refresh;…
- Excel VBA Sub or Function Not Defined? Fix It“Sub or Function not defined” is a VBA compile error: Excel cannot resolve the name of a procedure…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.