Comparing worksheets in excelClosed

Question

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.

vcoolio · Answer

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 SubSub SheetComparison(Sheet1 As String, Sheet2 As String)Dim cell As RangeDim 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 NextMsgBox diff & " differences found", vbInformation, "Check Differences"ActiveWorkbook.Sheets(Sheet2).SelectEnd 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.

Comparing worksheets in excel

1 response

Similar discussions

Subscribe To Our Newsletter!