Macro To Compare Two Excel Spreadsheets And Highlight Differences

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.

Creating Macro

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.

Figure 1

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.

Figure 3

Copy below code and paste it in the editor.

Save this Macro by closing the editor.

Running Macro

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.

Editing Macro

  • 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.

  1. I put my comment wrongly on this page but I did actually try the other macro. FOr some reason, it did not work even after several attempts. I had given up on that but now that I have a work around, at leisure, I will troubleshoot the macro itself and will keep you posted. I dont doubt your macro a bit, it must be something that I am doing. I have several named ranges and drop downs….Any, when I figure it out, I am sure ly going to look like a fool, that much I can tell..Have a good week.

  2. Well, wish you all the very best and thank you for visiting Vertical Horizons. Keep providing your feedback/suggestions and let us help you out in every possible way we can…!!

    We will be looking forward to your updates on trial with above Macro.

  3. Hi Admin,

    Thank you so much for creating this post for me, it’s greatly appreaciated. now this is the first of the two new codes you have provided for me that I am trying and it’s not working, i’ve de bugged it and it states it’s not liking this line;

    If diffB Then
    DiffCount = DiffCount + 1
    ws2.Cells(i, c).Select

    The ws2.Cells (i,c).Select is highlighted in yellow.

    Any suggestions?? I’m away to go and run the other macro you created to see if I can get this to work, and will post my findings very shortly.

    once again, thanks for all the help you have supplied me over the last few days.

    Andi x

  4. Hi ,

    I want to craete macro which will compare Name column in two workbooks and display the names in 2nd worksheet even if they don’t exist in 1st workbook with data 0.

    Please help me

  5. Hi,

    I have two workbooks with 1 sheet each. and I want to compare data in two workbooks with column”Name”. If Name in sheet2 doesn’t match with name in sheet1, even then it should display the name in sheet 2 with data 0 across all fields.

  6. Hi,

    I’ve got a comparison Macro working well with one minor issue. For some reason if I use the number 1 in some of the cells to differenciate that cell from the opposing sheet, the macro doesn’t identify the difference until I change it to anything but a 1. Strange ?

  7. Hi Jeremy,

    Assuming that you are using the same macro as posted here, we have not been able to reproduce this issue.

    Let us know how can we help you further.

    Regards

  8. Hi
    i am trying to compare columns in two different worksheets which has data in the form of alphabets.
    When I tried this, it shows me a run time error 1004 ; select method of range class failed
    please help me with this

    thanks

  9. Thanks for this macro! I used it to compare two parameter files from two different machines and it works great! Some nuances though: 1: The macro must be started in worksheet#2 or it won’t work; 2: If you run the macro, the differences are shown on worksheet#2. Should a correction be made on worksheet #1 and a second comparison is made, your original differences shown in colored cells won’t return to standard format even though they now match. No big deal.

    Thanks again!

  10. Hi,
    This is an amazing tool, thanks for these article! Trying to develop something similar within Excel, maybe you have some pointers as to how it might work?

    So we’re comparing data from two spreadsheets, with two columns. A given row of Spreadsheet 1 will have the same data of those two columns in a differently positioned row in Spreadsheet 2. Is there any way to highlight any differences between the data of a row in Spreadsheet 1 and 2 even though the rows of each spreadsheet are in a different order?

    Any quick pointers would be fantastic!

    Thanks!

  11. Hi Jack,

    This macro will get you the desired results as it doesn’t care of row ordering. Isn’t it working for you with the given scenario?

    Let us know and we will be glad to help you out.

    Regards

  12. Hi there

    does this macro assume the same number of columns and rows on each sheet being compared?

    I seem to be getting the same error Andi’s post:
    If diffB Then
    DiffCount = DiffCount + 1
    ws2.Cells(i, c).Select

    The ws2.Cells (i,c).Select is highlighted in yellow.

    Any ideas on what may be causing the problem?

    Regards
    Alexi

    • Hello!

      I am not sure, but for me it worked. I had the same problem, it highlighted in yellow
      ws2.Cells (i,c).Select
      and the way I solved the problem was to insert in each of the tables from Sheet1 & Sheet2 one empty row at the end. I have no logical explanation, but maybe this will be useful for some of you!

      Good luck!

  13. Hey,
    I’ve used this macro, which works brilliantly for a number of scenarios. As we have to reverse the macro to check Sheet 2 against Sheet 1, we have created a reverse macro that pastes these differences into Sheet 3. This means both sets of difference are pasted into Sheet 3 on top of each other, which is a little messy. How do we define where the macro posts the differences? Ideally we’d like to be able to paste our reverse results directly underneath the first set of results found with the macro above, directly underneath these, rather than on top. So essentially we’re asking how does the macro define where to post the results in Sheet 3?

    Thanks, any help would be appreciated!

  14. Hi,

    Macro worked well and I’ve made many more improvements for my own processes. One thing – the current macro above highlights the whole row yellow where there is a difference. Is there anyway to change that so the macro still copies the whole row across to Sheet 3 but only highlights the individual cell yellow where the difference exists?

    Thanks, any help would be greatly appreciated!

  15. Hi
    I have 2 diffrent spreadsheets.I need to compare 2 columns from these and display the result in 3 spreadsheet.
    Can you please help me write macro on this.

  16. Hi,
    I have 2 different Excel files and
    I need to compare those files
    if there is a changes/update and a new data.
    And display the result in 2 different files
    which is the changes/update data must be highlights and
    the new data also on a seperate file.
    can you please help me on this.
    I need a macro to do the comparing. Thanks!

  17. hello Admin ,

    thank you alot for those macros, could please help me in this subject:
    2 tables with diffrent size
    colomn1: contains files_name (first files_name begin at cell A2)
    row1: contains code_error_name(first code_error_name at cell B1)
    files_name and code_error_name may change position or be deleted between the two sheets so we need to make a search in colomn and rows to comprae data in the 2 tables .

    so I guess that the macro need to this , but I can’t do it in VBA :)
    search files_name from colomnA.sheet2 in ColomnA.sheet1
    if found
    search code_error_name from Row1.sheet2 in Row1.sheet1
    if found
    recuperate the coordinate of (files_name, code_error_name) from the two sheets than

    compare value in cell (files_name, code_error_name) in sheet2 with value cell (files_name, code_error_name) in sheet1 —> than highlight with diffrent color if not equal
    do this operation for all the table

    if it’s not clear I can send to you the two tables. Thank you alot for your help

  18. I for some reason can not get any compare vb script to work on the spreadsheets i have to analyze.. Its driving me absolutely crazy!!!

    this script hangs at:

    ws2.Cells(i, c).Select

    Any ideas on whats going wrong????

    PLEASE HELP

    thank you

  19. I used the script above and runs well , the only addition i want is if the cells r blank or new rows or colums are added it doesn’t show a mismatch , a modified script with above additions would be highly appreciated , waiting eagerly for an reply

  20. Hello,

    I am also comparing 2 sheet to try and highlight differences.
    Each day i receive a new sheet on which there may be new rows, updated cells or deleted rows and i need to highlight these so i can update my system.
    When i run the above it says calculating cells, runs all the way to 100%, then back to 1% and counts up again but only gets to 26% before crashing Excel.

    Can you help please?!?!

    Thanks!

  21. Hi,

    am working on similar thing, where i need to highlight the differences found in the different columns in the same row in the same spreadsheet. Is there any way to do it?

  22. Works brilliantly – the only thing I have to say is that the Msg Box shows an inaccurate number of non-matching cells (30 instead of 4). Any way to solve that?

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">