Compare Two Excel Sheets and Highlight Differences Macro

In this article, we will learn how to compare two Excel sheets and highlight differences using a macro. So, let’s start. Sometimes we are working on two different sheets or two different workbooks, and there are some similarities in the data. In that case, if we highlight the differences, we can easily get the matched values. But highlighting differences is quite tough, especially if the dataset is huge. So, we can use a VBA Macro to execute this process.

How to Use VBA Macro to Compare Two Excel Sheets in Same Workbook and Highlight Differences

The dataset used in this article shows the Revenue and Profit for the following year of two sister concern companies, ABC Enterprise, and XYM Motors. There are some differences in the P&L sheets of these two companies. Here we will show you the detailed procedure to compare the P&L sheets of these two companies and highlight the differences using macro. So, follow the below steps to execute the full process.

Here, we will complete the two different P&L sheets of these two companies. These two companies show their Revenue and profit, and the output of these points is almost the same. Therefore, we will use VBA (Visual Basic Analysis) code to compare and then highlight the differences. Now follow the steps to execute the program

• Initially, open the Module to write down the VBA program.
• Then, write down the Code to execute this process.

Sub CompareSheets()
Dim wkst1 As Worksheet, wkst2 As Worksheet
Dim cell1 As Range, cell2 As Range
Dim diffCount As Integer
Set wkst1 = ThisWorkbook.Sheets("First Sheet")
Set wkst2 = ThisWorkbook.Sheets("Second Sheet")
diffCount = 0
For Each cell1 In wkst1.UsedRange
Set cell2 = wkst2.Range(cell1.Address)
If cell1.Value <> cell2.Value Then
cell1.Interior.Color = RGB(173, 216, 230)
cell2.Interior.Color = RGB(173, 216, 230)        
diffCount = diffCount + 1
End If
Next cell1 
MsgBox diffCount & _
" differences found_after comparing two sheets", _
vbInformation
End Sub

Sub CompareSheets()
Dim wkst1 As Worksheet, wkst2 As Worksheet
Dim cell1 As Range, cell2 As Range
Dim diffCount As Integer   
Set wkst1 = ThisWorkbook.Sheets("First Sheet")
Set wkst2 = ThisWorkbook.Sheets("Second Sheet")

diffCount = 0
For Each cell1 In wkst1.UsedRange
Set cell2 = wkst2.Range(cell1.Address)
If cell1.Value <> cell2.Value Then
cell1.Interior.Color = RGB(173, 216, 230)
cell2.Interior.Color = RGB(173, 216, 230)           
diffCount = diffCount + 1
End If
Next cell1  
MsgBox diffCount & _
" differences found_after comparing two sheets", _
vbInformation
End Sub

• After completing the program, add a button by selecting Developer >> Insert >> Button (Icon).

• Therefore, assign the program after the Assign Macro dialog box pops up.
• Select the program and click OK to assign the macro.

• Now, edit the button name by right-clicking the mouse and selecting Edit Text from the option bar.

Note: You can also change formatting with Format Control. But this is absolutely optional and avoidable.

• Finally, The final output will be similar to the one below.

How to Use VBA Macro to Compare Two Excel Sheets from Different Workbooks and Highlight the Differences

In this method, we will compare two Excel sheets from different workbooks and highlight the differences. Sometimes we need to work on two similar datasets at the same time in different workbooks, but when you are working on almost identical datasets, you need to find the differences as well. So, the two workbooks here. First one is Compare Sheets and Another File.

• In the beginning, go to the VBA Module using the process already shown before.
• Now, write down the code for the following module and get the final output.

Sub CompareSheets()
Dim wkbk1 As Workbook, wkbk2 As Workbook
Dim wkst1 As Worksheet, wkst2 As Worksheet
Dim cell1 As Range, cell2 As Range
Dim diffCount As Integer
Set wkbk1 = _
Workbooks.Open("C:\Users\Nafisa\Downloads\Excel Compare 2 shhets .xlsm")
Set wkbk2 = Workbooks.Open("C:\Users\Nafisa\Downloads\Another File.xlsm")
Set wkst1 = wb1.Sheets("First Sheet")
Set wkst2 = wb2.Sheets("Second Sheet")
diffCount = 0
For Each cell1 In wkst1.UsedRange
Set cell2 = wkst2.Range(cell1.Address)
If cell1.Value <> cell2.Value Then
cell1.Interior.Color = RGB(173, 216, 230)
cell2.Interior.Color = RGB(173, 216, 230)          
diffCount = diffCount + 1
End If
Next cell1
MsgBox diffCount & " differences found.", vbInformation
wkbk1.Close False
wkbk2.Close False
End Sub

Sub CompareSheets()
Dim wkbk1 As Workbook, wkbk2 As Workbook
Dim wkst1 As Worksheet, wkst2 As Worksheet
Dim cell1 As Range, cell2 As Range
Dim diffCount As Integer

Set wkbk1 = _
Workbooks.Open("C:\Users\Nafisa\Downloads\Excel Compare 2 shhets .xlsm")
Set wkbk2 = Workbooks.Open("C:\Users\Nafisa\Downloads\Another File.xlsm")
Set wkst1 = wb1.Sheets("First Sheet")
Set wkst2 = wb2.Sheets("Second Sheet")
diffCount = 0

For Each cell1 In wkst1.UsedRange
Set cell2 = wkst2.Range(cell1.Address)
If cell1.Value <> cell2.Value Then
cell1.Interior.Color = RGB(173, 216, 230)
cell2.Interior.Color = RGB(173, 216, 230)           
diffCount = diffCount + 1
End If
Next cell1
MsgBox diffCount & " differences found.", vbInformation
wkbk1.Close False
wkbk2.Close False
End Sub

• Finally, the output will be similar to below.

How to Compare Two Sheets and Highlight Duplicates in Excel

Here, we will compare two sheets and highlight duplicates in Excel using conditional formatting.

• Initially, select cell range C5:D16 and go to the Home tab >> Conditional Formatting >> New Rule.

• Write the formula in the “Format values where the formula is true” option and click OK as below.

• Now, finally, the duplicates are highlighted, and the final output will be similar to the one below.

Read More: How to Compare Two Excel Sheets for Differences in Values

How to Compare Two Sheets and Highlight Differences Using VLOOKUP in Excel

Here we will compare two sheets and highlight differences using the VLOOKUP function in Excel.

• Initially, select cell E5 and enter the formula to execute this process.

Read More: How to Compare Sheets Side by Side in Excel

Things to Remember

• Be careful while entering the path of the workbook in the code. If the path is not right, then the program will not run, and you can not complete the process.
• Always enable macro in Excel so that once you open a file that includes you don’t see any restriction working on the file.

Download Practice Workbook

You can find the practice sheet here.

Conclusion

In this article, we learned how to compare two Excel sheets and highlight differences using macro and different methods. Here you can find the differences and highlight them within two sheets or two different workbooks as well. Therefore we covered every possible way to execute this process. So, Hopefully, you can solve the problem shown in this article. Please let us know in the comment section if there are any queries or suggestions.