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

 

[sumuwin]

Hi

maybe something like this, passing the sheetnames to the sub

Sub RunCompare()

Call compareSheets("Before", "After")

End Sub


Sub compareSheets(shtBefore As String, shtAfter As String)

Dim mycell As Range
Dim mydiffs As Integer
    
For Each mycell In ActiveWorkbook.Worksheets(shtAfter).UsedRange
    If Not mycell.Value = ActiveWorkbook.Worksheets(shtBefore).Cells(mycell.Row, mycell.Column).Value Then
        
        mycell.Interior.Color = vbYellow
        mydiffs = mydiffs + 1
        
    End If
Next

MsgBox mydiffs & " differences found", vbInformation

ActiveWorkbook.Sheets(shtAfter).Select

End Sub

atb
sumuwin

 

[PaulNelson]

Re: Compare Two Sheets and Highlight Differences (Sheet attached) (SOLVED)

Thanks [FONT=&quot]sumuwin!

This was right on the money! +1 to you

I've seen other solutions around but this is by far the most simple and elegant use of VB.

Here is an alternate version for anyone who wants to use the names Sheet1 and Sheet2 as per the default in excel.

[/FONT]

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

[FONT=&quot]
[/FONT]

 

[PaulNelson]

How would I ignore dates in the comparison? The data I am using will always have different dates for records and I want to ignore them. They are being exported from SAP in a .xls report and I don't think the fields will be formatted as a date, but will rather be a plain text field with a date in it. Thus, it would have to be something along the lines of ignoring '00/00/0000".

Any help would be appreciated.

 

[sumuwin]

Hi Paul

try replacing the For...Next code with the attached. This should ignore anything that is in a date format

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

atb
sumuwin

 

[PaulNelson]

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

 

[maxsun08]

Hi,

I found this post very helpful but was wondering if it can compare by values versus by rows. For example, my "before" and "after" worksheets are personnel rosters for "yesterday" and "today", respectively, but if my "before" roster doesn't have a an employee first, last, etc. most of "after" sheet is highlighted because its comparing row by row.

Is there a way it could find differences using col A and B as unique identifiers?

Many thanks in advance! This has been stumping me for two days now.

Cheers

 

[CWISE]

I have the same issue as maxsun08. I used the code and it works great until I have an addition in the one sheet that wasn't on the other sheet, so everything after is highlighted in yellow.

Basically I have list of employees in March need to compare to April and find differences, noted in April or separate sheet.

I have tried conditional formatting, but I was expecting all the differences to be noted in April and would expect that the same ones would be highlighted in March, however, that is not the case. I then tried to have all the fields that have conditional formatting to identified via GoToSpecial - conditional formatting and again it didn't do anything. So started looking to VB code. Have the same issue due to new hires inserted as report is alphabetical.

 

[junjunbjtu]

when I used this macro, it worked well in most sheets. But there is an error when I used it on one sheet. It hinted "run-time" error '13', Type mismatch. I don't know the reason. But I am guessing there is something wrong about the type of my sheet.

 

[RoryA]

If you have any error values on the sheet, you would get that error.