Macro To Compare Two Columns in Excel Spreadsheet And Highlight Differences

While we provided solution for comparing two columns in excel spreadsheet and highlight the duplicates, we realized that requirement can be reverse of it as the title of this post suggests – compare and highlight different entries in two columns of same excel worksheet. Here is a step by step procedure to compare data in two columns of same worksheet in excel and highlight the different entries. This Macro compares column 2 with column 1 and highlight the different entries in column 2. It 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 active sheet (Sheet1 in this example) and run the Macro by clicking on Tools->Macro->Macros. Select the Macro “Compare” and click on “Run” button. Different entries will be highlighted in second column.

In the above Macro, it is assumed that first row is a header row. Also, worksheet names is set as “Sheet1″ (refer variable WS in above macro) and columns to be compared are considered to be “1″ and “2″ (refer variables c1 and c2). To change the worksheet names/column numbers 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.
  • Customize the code as per the requirements.
  • Save the Macro.

Hope you find this article useful, your suggestions and feedback are always welcome. Thank You.

  1. I tried using the scripts it works good except for once case.
    One Column contained 1 and other column contained 0 in the same row.
    The difference was not detected at all.
    Pls let me know why

  2. I truly love your blog.. Excellent colors & theme.
    Didd you make this amazing site yourself?
    Please reply back as I’m attempting to create my very own site
    and would love too leardn where you got this
    from or exactly what the theme is named. Thasnk you!

  3. Hey, I think your site might be having browser compatibility issues.
    When I look at youyr website in Chrome, iit looks fine but when opening in Internet Explorer, it has some overlapping.

    I just wanted to give you a quick heads up! Other then that,
    very good blog!

  4. あなた実際プレゼンテーションしかし | | 本当に簡単そう簡単それは見えるようです作るI 見つけることにこのトピック 私は思うことを 一つのこと 私がかもしれない 決して理解しています。 複雑な複雑とすぎに思え 非常に ワイド私のために。公開| |提出| 私は私はプットアップ、私がしますぶら下げる|しがみつく|把握|ホールド|} 私は {しようとするでしょう!
    セール対象商品 送料無料

  5. こんにちは |あなたの存在、私が発見し、私が見つかりました |ながら、同時にGoogleのさえとして 問題、あなたのサイト 思い付いた、それは 偉大|}のように{良いように見えます|のようですが表示されます。 私がしたのGoogleブックマークにそれをブックマークし。
    一流の素材 メール便送料無料

  6. Its such as you read my thoughts! You seem to understand so much
    about this, such as you wrote the e-book in it orr something.

    I believe that you can do with a few p.c. to power the message
    home a little bit, however other than that, that
    is wonderful blog. A great read. I will certainly be back.

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="">