Compare Two Workbooks Excel VBA Macro

In this video we see how to compare two Excel workbooks with VBA macros. The macro checks if the number of worksheets matches, then compares the worksheet names and the contents in the sheets with the same name (this last piece of code has been covered in a previous video: Compare Two Worksheets). Whenever there is a mismatch, it highlights the sheet or cells with different values in yellow, and displays a message with cell and worksheet (this could also be copied to a separate sheet instead). Note that the macro compares the workbook with the macro (ThisWorkbook) and a second open workbook. Optionally, a dialog to open the second file, or even the two workbooks could be implemented at the start if need be. Find the code to compare two workbooks with Excel VBA in the Excel Macro Class blog under the following link: excelmacroclass.blogspot.com/...
You can find many other macro examples and Excel VBA learning materials (including the Excel VBA Guide for Beginners) in the blog under the link: excelmacroclass.blogspot.com/
And yet, if you want more, you can find various Excel applications of different nature in the other blogs of the Excel Macro Mania saga:
Excel Macro Fun (excelmacrofun.blogspot.com/)
Excel Macro Business (excelmacrobusiness.blogspot.com/)
Excel Macro Sports (excelmacrosports.blogspot.com/)

[@sprittube7237]

Like all your videos...👍

[@MohAboAbdo]

Thank you so much

[@121960864]

thank you soooo much!

[@rehanchaudhry951]

Great video! I'm using this code but need help adjust the coding to (1) loop through rows starting from row 4 to the last row and (2) only perform this code on a few of the sheets, not all of them. Can you help? Thanks, in advance!

[@rehanchaudhry951]

fwiw I changed the "A1" range reference for ws1 to "A4" so the macro would loop from there through every cell, but it keeps checking all the cells. I also tried to changing the "cell" references to "row" but that just caused errors.

[@ExcelMacroMania]

@@rehanchaudhry951 There are several ways to do that. You can use a For loop instead of For Each. For example:
For r = 4 To lastRow
For c = 1 To lastCol
Set cell = Cells(r, c)
...
If you want to keep the For Each loop, you need to specify the range, for example for data in columns A to D starting in row 4 as you said would be:
For Each cell In ws1.Range("A4:D" & lastRow)
...

[@yachimomo]

If instead of comparing all cells in both Worksheets, suppose I want to compare only 2 specific cells then what change should I make to the code you told above

[@ExcelMacroMania]

If it's only 2 cells you can have the 2 conditions but for more than 2 is better to use a range so I show you the code using a range or 2 cells: C3 and H5. You can add more cells there using the comma separator. Then loop for each cell in that range and check if they have the same value in ws1 and ws2.
Dim rng As Range, ws1 As Worksheet, ws2 As Worksheet
Set rng = Range("C3,H5")
Set ws1 = Sheets(1) 'or Sheets("sheetname") or any other way
Set ws2 = Sheets(2)
For Each cell In rng
If ws1.Range(cell.Address) = ws2.Range(cell.Address) Then
ws1.Range(cell.Address).Interior.Color = vbYellow
End If
Next cell

[@varshak.s8182]

How to highlight the column header also if any mismatches

[@ExcelMacroMania]

I reply to your similar question in the video about comparing worksheets. For the code in the post - link in description, that would be something like this:
Cells(1, cell.Column).Interior.Color = vbYellow

[@adityachoudhary4306]

How do i make a dialogue to select the workbboks

[@ExcelMacroMania]

You can use Application.GetOpenFileName or Application.FileDialog. Both are explained towards the mid-end of this video about the Application object: ruclips.net/video/zCMxvxDZ1rw/видео.html

[@omprasaddehury2109]

I have 2 sheets in a single work book named March and April.
Here 4 columns are there in both the sheets having same names.
So the requirement is I have to compare A column of sheet1 with A column in Sheet2 , B column of sheet1 with B column in Sheet2,C column of sheet1 with C column in Sheet2 and D column of sheet1 with D column in Sheet2 and highlight the duplicate cells using Macro VBA.
If you can share the code it would be great.

[@ExcelMacroMania]

All you need is in these other two videos
1. Highlight duplicates: ruclips.net/video/VohG3ae_A98/видео.html
2. Compare two worksheets: ruclips.net/video/hZIFnKetyI0/видео.html

[@omprasaddehury2109]

@@ExcelMacroMania I have tried but it is not working correctly. Could you please provide me the updated code that will be really helpful.

[@ExcelMacroMania]

Have a look at this new video, I think that's what you are looking for - Compare Two Columns: ruclips.net/video/UvnefIMtNnM/видео.html

[@premshilu]

it is not giving any output
nothing is happening when i hit the run

[@ExcelMacroMania]

That's probably because the two workbooks you are comparing are exactly the same, as per the properties that are being compared with this macro (number or sheets, name of sheets, and content cell-to-cell for each sheet with the same name). Otherwise, there might be some error in your code. You can get the code here: excelmacroclass.blogspot.com/2022/02/compare-two-workbooks.html

[@uyenhoanglephuong3883]

@@ExcelMacroMania But it seem to be compared with itself. I have tried to read out wb1.Worksheets.Count and wb2.Worksheets.Count. Both are same number of count even their number of sheets are not same. I think the second Workbook(2) is not work. is there any steps that I missed?

[@ExcelMacroMania]

Oh, that's why the "second" workbook was actually opened first, so to speak. I wrote Workbooks(2) for simplicity, but is better to specified the exact name of the workbook: Set wb2 = Workbooks("Book2.xlsx"")

[@uyenhoanglephuong3883]

@@ExcelMacroMania thank you very much for your feedback. it works properly. your video is very helpful ❤