# Compare Two Excel Sheets and Highlight Differences Macro

Get FREE Advanced Excel Exercises with Solutions!

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 ``````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
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``````

Code Breakdown

``````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")``````
• The name of this program is CompareSheets and worksheets are declared as wkst1 & wkst2. Cell1 and cell2 are declared as Range and diffCount as Integer.
• Then, select the worksheet’s name to identify the sheets.
``````diffCount = 0
For Each cell1 In wkst1.UsedRange
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``````
• If the program finds different value then this program will highlight that cell using RGB option and show msgbox before ending the program.
• 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 = _
Set wkst1 = wb1.Sheets("First Sheet")
Set wkst2 = wb2.Sheets("Second Sheet")
diffCount = 0
For Each cell1 In wkst1.UsedRange
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``````

Code Breakdown

``````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``````
• Here, workbook 1 and 2 is declared as wkbk 1 and wkbk 2, sheets are declared as wkst 1 and wkst 2, cell 1 and cell 2 is declared as Range, difference counts as Integer, and the code is named as Compare Sheets.
``````Set wkbk1 = _
Set wkst1 = wb1.Sheets("First Sheet")
Set wkst2 = wb2.Sheets("Second Sheet")
diffCount = 0``````
• Now, allocate the sheet and workbook paths and set this part.
``````For Each cell1 In wkst1.UsedRange
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``````
• Now, select the cell 1 address and cell 2 address and run the if statement. If the value matches the formula, then differences will be highlighted with the selected color, and here, add the message box to show the message.
• 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.
`=IFNA(VLOOKUP(D5,'[Excel_Compare_2_shhets_and_highlight_using_macro_.xlsm]Sheet1 (2)'!\$D\$5:\$D\$16,1,0),"Doesnot Match")` Formula Breakdown

VLOOKUP(D5,'[Excel_Compare_2_shhets_and_highlight_using_macro_.xlsm]Sheet1 (2)’!\$D\$5:\$D\$16,1,0)

• Here, the VLOOKUP function finds a match from another workbook and returns the match if the formula matches the value.

IFNA(VLOOKUP(D5,'[Excel_Compare_2_shhets_and_highlight_using_macro_.xlsm]Sheet1 (2)’!\$D\$5:\$D\$16,1,0),”Doesnot Match”)

• This formula will return value if it matches any value; otherwise, it will return “Doesnot Match”.

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.

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. Afrina Nafisa

Hey! This is Afrina Nafisa. Graduated from Ahsanullah University of Science and Technology. Currently working as a content developer in ExcelDemy. Working on myself for being better every day to make better content.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  