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
- 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.
- 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.
- Change
Sheet1andSheet2in 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. - Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm) if you want to keep the macro in that file. - Return to Excel and press Alt+F8, select
CompareTwoSheets, and choose Run. Review theCompare Resultstab.
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
Formulaproperty instead ofValue2. - 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 Resultsfor 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:
ThisWorkbookmeans 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
- Remove Duplicates in Excel: Shortcut and Safe Steps
- Remove Duplicates by Row in Excel
- Remove Both Duplicates in Excel: Keep One or Remove All Rows?
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Optimize Copy-Paste Formulas in VBATo optimize formula copying in Excel VBA, assign a formula to an entire destination range at once instead…
- VBA MsgBox Buttons: Options, Examples, and Return ValuesIn VBA, the MsgBox function’s buttons argument controls which buttons and icon appear, which button is selected by…
- Excel VBA Date Now: Get Current Date and Time in MacrosIn Excel VBA, use Now to get the current date and time from the computer’s system clock. For…
- VBA Code to Consolidate Excel Files into One WorkbookTo consolidate matching Excel files into one worksheet, place the macro below in the workbook you want to…
- Excel VBA MsgBox Custom Buttons and ChoicesExcel VBA’s built-in MsgBox cannot create arbitrary button captions such as “Continue” or “Skip”; it offers fixed button…
- VBA Get Length of String: How the Len Function WorksTo get the length of a string in VBA, use the Len function: Len(myText) returns the number of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.