Continuing with our series of excel macros, here is a step by step procedure to compare data in two excel spreadsheets and highlight the different entries. This Macro compares second worksheet with first worksheet and highlight the different entries in second worksheet. This Macro also updates the operation’s progress in status bar and helps user in estimating the time taken for comparison.
First step is to create a Macro. For creating a macro, click on Tools in menu bar, select Macro and then Macros in sub menu as shown in Figure 1 below.
Clicking on Macros in Figure 1 will open the below window. Type the name of Macro as “Compare” or a name of your choice and click on Create button.
Clicking on Create button will launch the Visual Basic editor as below.
Copy below code and paste it in the editor.
' Macro1 Macro
' compare two different worksheets in the active workbook
CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim diffB As Boolean
Dim r As Long, c As Integer, m As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Application.DisplayAlerts = True
lr1 = .Rows.count
lc1 = .Columns.count
lr2 = .Rows.count
lc2 = .Columns.count
maxR = lr1
maxC = lc1
If maxR < lr2 Then maxR = lr2
If maxC < lc2 Then maxC = lc2
DiffCount = 0
For c = 1 To maxC
For i = 2 To lr2
diffB = True
Application.StatusBar = "Comparing cells " & Format(i / maxR, "0 %") & "..."
For r = 2 To lr1
cf1 = ""
cf2 = ""
On Error Resume Next
cf1 = ws1.Cells(r, c).FormulaLocal
cf2 = ws2.Cells(i, c).FormulaLocal
On Error GoTo 0
If cf1 = cf2 Then
diffB = False
If diffB Then
DiffCount = DiffCount + 1
Selection.Font.Bold = True
ws2.Cells(i, c).Interior.ColorIndex = 19
Application.StatusBar = "Formatting the report..."
'Columns("A:IV").ColumnWidth = 10
m = maxR - DiffCount - 1
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox m & " cells contain different values!", vbInformation, _
"Compare " & ws1.Name & " with " & ws2.Name
Save this Macro by closing the editor.
Select Sheet1 and run the Macro by clicking on Tools->Macro->Macros. Select the Macro “Compare” and click on “Run” button. Duplicate entries will be highlighted in Sheet1.
In the above Macro, it is assumed that first row is a header row. Also, worksheet names are considered to be “Sheet1″ and “Sheet2″. To change the worksheet names or customize this Macro further, follow below steps.
- Go to Tools->Macro->Macros. Select the Macro “Compare” and click on “Edit” button.
- This will launch the Visual Basic Editor.
- Find the string “CompareWorksheets” in the code.
- In this line change the name of worksheets from “Sheet1” and “Sheet2” to the ones required.
- Save the Macro.
Hope you find this article useful, your suggestions and feedback are always welcome. Thank You.