Previous Topic Next Topic

Comparing worksheets in excel

Closed
Hannah - Nov 21, 2014 at 07:03 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Nov 21, 2014 at 11:31 PM
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 response

Answer 1 / 1
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Nov 21, 2014 at 11:31 PM
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.
0

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses