Compare Two Sheets and Highlight Differences (Sheet attached)

[PaulNelson]

Hi all,

I've been searching for an answer to this question on the internet and there are lots of similar things but nothing does exactly what I want it to do and I'm a bit of a VBA noobie.

I am trying to compare two sheets within the same workbook (Sheet1 = Before and Sheet2 = After) row by row.

I don't want to change anything in the Before sheet, but I want to compare the two and if there are any updates, deletions/modifications between the two sheets, I want to highlight them in yellow on the After sheet. I want the range to be dynamic so that different sets of data can be used with this.

Also, if there could be a pop up that says something along the following lines, it would be awesome! "There were 55 differences detected in the before and after worksheets!"

I hope that's not too much to ask, I know the excel guru's here shouldn't have any problem and the help is greatly appreciated!

Here is the link to my sheet as an example of what I want to accomplish. I can provide a bigger data set if required, just let me know!

Example spreadsheet comparison

 

[Sgdva]

...
I am not a coder and do want to learn ...

OT: If you really want to learn, debug it and see what each line does, define how the workflow for Excel should be performed and then translate it in coding, suit for your needs.
The code works as long the rows and columns are the same size in both sheets -otherwise is not possible compare a matrix 1 2 3 4 to a 1 3 4, in your case 1-10 vs 1-5 and 7-10 there are too many scenarios and data would be totally different, throwing differences even if they don't exist for the data you want to compare- , that's precisely why I extended it, it will tell where you can "fake" the row/column that seems to be missing among the sheets by adding a "dummy" blank one.
It starts to color when the data seems to be totally missing, so you know where to "fake" (by adding a blank row/column depending on case) the row -or column- that was deleted in the new document vs the old one (or vice versa).
Summary: It highlights row 6 because that's where it seems data is missing (macro needs compare xsize vs xsize, it can't compare 10 vs 9), if you want to compare 1-10 in Sheet1 vs 1-5 and 7-10 in Sheet2, add a blank row as 6 and then it will be able to compare the sheets -Of course, it will highlight 6 since it doesn't exist in the other one-.
It will highlight another differences as it was intended.

 

[Kanchi]

Hello,
I came across this excel and it works fine except for a scenario where i have 2 separate excel sheets and i would like to compare each worksheet by worksheet of the 2 excels and also consider the different if there in the decimal places.
Can you please let me know the code.

 

[Sgdva]

Sorry, the code was meant to work in the same excel workbook
Perhaps you could copy the sheet from the workbook2 and paste it in workbook1 and try it?
Difference is noted as long the cells are not the same -includes decimals, blank spaces placed different,etc-

 

[Mayur7]

search, compare, update and indicate data in excel sheet 2 from sheet 1 with macros VBA
Q) Core excel sheet 1 with 10 to 15 thousands rows and around 150 columns. Another targeted sheet 2 to be completed with using VBA which content same 10-15 thousands row but selected 25- 30 columns.
Following operation must be done to get targeted sheet.

Button for performing below operation
Searching unique ID number from sheet2 to core sheet1.
Compare each row
If any change than update in sheet2
Colour the update
Indicate the row which is updated

I am new to VBA programming.
I will be really happy if some one help me to find out solution for the problem as early as possible.

 

[Kurt]

You my friend, are brilliant!

This is the solution for anyone interested.

Sub RunCompare()
[COLOR=olive]'Call the compareSheets routine[/COLOR]
Call compareSheets("Before", "After")
End Sub

Sub compareSheets(shtBefore As String, shtAfter As String)
Dim mycell As Range
Dim mydiffs As Integer
 
[COLOR=olive]'If current cell is not a date then proceed (else skip and go to next), then 'if not same as corresponding cell in sheet After, [/COLOR]
[COLOR=olive]'mark as yellow and repeat until entire range is used[/COLOR] 
 
For Each mycell In ActiveWorkbook.Worksheets(shtAfter).UsedRange
If Not IsDate(mycell) Then
    If Not mycell.Value = ActiveWorkbook.Worksheets(shtBefore).Cells(mycell.Row, mycell.Column).Value Then
 
        mycell.Interior.Color = vbYellow
        mydiffs = mydiffs + 1
 
    End If
End If
Next
 
[COLOR=olive]'Display a message box stating the number of differences found[/COLOR]
MsgBox mydiffs & " differences found", vbInformation
ActiveWorkbook.Sheets(shtAfter).Select
End Sub

I found this old post while doing a search.

Is there a way to reuse this type of code and have Excel 2010 compare the same column in two different worksheets even if the column in Worksheet 1 is in column D and let's say in Worksheet 2 the column is E?

 

[msorgen3]

I don't know VBA. Is there a formula I can use to compare name fields in two different sheets? Sheet 1 has the data in col B. Sheet 2 has it in column A .

 

[Sgdva]

I don't know VBA. Is there a formula I can use to compare name fields in two different sheets? Sheet 1 has the data in col B. Sheet 2 has it in column A .

Why not look by titles then set the columns in the same position before calling the compare?

 

[msorgen3]

Why not look by titles then set the columns in the same position before calling the compare?

Because if one of them is off, then all of them would be off as well. I would like whatever was wrong to be highlighted.

I saw the vba answer before. Where would I stick it?

 

[Sgdva]

Because if one of them is off, then all of them would be off as well. I would like whatever was wrong to be highlighted.

I saw the vba answer before. Where would I stick it?

Try a few pages back, I posted the code
Alt + f4 paste it on a normal module.

 

[seeorana]

Re: Compare Two Sheets and Highlight Differences

I found the code in this questions and it is excellent for what I am trying to achieve. (I am not concerned about dates)
In my spreadsheets that I wish to compare, I only want to compare the first 5 (five) columns.

How can this code be altered to do this?

Any assistance would be most appreciated.


Sub RunCompare()

Call compareSheets("Sheet1", "Sheet2")

End Sub


Sub compareSheets(shtSheet1 As String, shtSheet2 As String)

Dim mycell As Range
Dim mydiffs As Integer

'For each cell in sheet2 that is not the same in Sheet1, color it yellow
For Each mycell In ActiveWorkbook.Worksheets(shtSheet2).UsedRange
If Not mycell.Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then

mycell.Interior.Color = vbYellow
mydiffs = mydiffs + 1

End If
Next

'Display a message box to demonstrate the differences
MsgBox mydiffs & " differences found", vbInformation

ActiveWorkbook.Sheets(shtSheet2).Select

End Sub