<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Compare Two Worksheets in Excel

Published on
15,709 Points
9,409 Views
3 Endorsements
Last Modified:
Approved
Justin Owens
We don't support machines, but rather, the people who rely upon them...
A user came to me today needing to quickly compare two Excel worksheets which were supposed to be the same but were not.  Because she needed a very quick answer, I did a quick Google search and found a nice macro which grabbed Sheet1, compared it to Sheet2, and then created a new workbook.  In the new workbook, any changed cell had both the original text and the changed text.  Now, I like to give credit where credit is due.  I found the original code for the macro here.

The problem was that the new workbook contained over 5000 changed cells.  Some of the cells had a text paragraph in them originally, so the new workbook contained cells with huge amounts of text separated by "<>".  It was a visual nightmare, as the user had a hard time quickly telling where the original stopped and the changed started.  I requested some help, and nutschfrom Experts-Exchange came to my rescue.  He came up with a quick macro which changed the color of the text after the separator mark (<>) to red.

Sub ChangeColor() 
Dim cl As range 
 
On Error Resume Next 
 
For Each cl In ActiveSheet.UsedRange.Cells 
 
    If Len(Trim$(cl)) > 0 Then 
        If InStr(cl, "<>") > 0 Then _ 
            cl.Characters(Start:=InStr(cl, "<>") + 2, Length:=Len(cl)).Font.ColorIndex = 3 
    End If 
Next 
 
End Sub

Open in new window


Here is the question on Experts-Exchange which resulted in this code:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25155367.html

After presenting this solution to the user, she again wanted something more.  This time, she said it would be better to just highlight the changes in Sheet2 in the original Workbook rather than concatenating the two respective cells when a change existed.  I once again turned to the Experts, and nutsch once again came to my rescue.  This code simply changes the text color in Sheet2 to red if it is different than the text in Sheet1.

Sub TrackDifferences() 
Dim rng As range, cl As range, sht2 As Worksheet, sht1 As Worksheet 
 
Set sht2 = Sheets("Sheet2") 
Set sht1 = Sheets("Sheet1") 
 
Set rng = sht1.UsedRange  'set variable rng to the range selected 
 
application.ScreenUpdating = False 
 
For Each cl In rng.Cells 'loop through all cells of the selected range 
    If sht2.Cells(cl.Row, cl.Column) <> cl Then _ 
        sht2.Cells(cl.Row, cl.Column).Interior.ColorIndex = 3 
Next 
 
application.ScreenUpdating = True 
End Sub

Open in new window


Here is the Question on Experts-Exchange which resulted in this code:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25155502.html

In the end, the user had two different options:  Option 1 was a new Workbook which had both old and new, and Option 2 just highlighted the changes directly in Sheet2.  I hope that someone can benefit from this comparison utility.

Justin
3
0 Comments

Featured Post

Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month