Comparing worksheets in excel [Closed]

Report
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
Hello,

I have 2 sets of data in Sheet1 and Sheet2 in excel. Both sheets have the same 7 columns and I want to compare the data in Sheet1 and Sheet2 and highlight in Sheet2 where there are any changes.

There are about 600 rows and there may be additional or fewer rows in Sheet2 compared to Sheet1.

There also isn't a unique identifier for each of the rows although no row will be duplicated across all 7 columns.

If anyone knows of an easy way to compare these it would be much appreciated! Do this on a regular basis and at the moment is a pain to do!

Thanks
H.

1 reply

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Hannah,

I have (well, actually my wife uses it where she works!) the following code which may help you:-

Sub RunComparison()

Call SheetComparison("Sheet1", "Sheet2")

End Sub

Sub SheetComparison(Sheet1 As String, Sheet2 As String)
Dim cell As Range
Dim diff As Integer
For Each cell In ActiveWorkbook.Worksheets(Sheet2).UsedRange
If Not cell.Value = ActiveWorkbook.Worksheets(Sheet1).Cells(cell.Row, cell.Column).Value Then

cell.Interior.ColorIndex = 8
diff = diff + 1
End If
Next
MsgBox diff & " differences found", vbInformation, "Check Differences"
ActiveWorkbook.Sheets(Sheet2).Select
End Sub


Wherever in the code you see Sheet1 or Sheet2, change it to suit your spread sheet tab names. The color index number you can change to suit yourself.

I hope this helps.

Regards,
vcoolio.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!