Compare Two Sheets and Highlight Differences

Topic

New Reply

[INDENT]I found the code in a post and it is excellent for what I am trying to achieve.
However, in my spreadsheets 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
[/INDENT]

Reply | Quote

Replies

Lot of ways to do this but some are faster than others.

HTH
Maud

Code:

Sub RunCompare()
     Call compareSheets(“Sheet1”, “Sheet2”)
End Sub

Sub compareSheets(shtSheet1 As String, shtSheet2 As String)
[COLOR=#008000]’——————————–
‘DECLARE AND SET VARIABLES[/COLOR]
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim Cell As Range, Col As Range, mycell As Range
    Dim mydiffs As Integer
    Set ws1 = Worksheets(shtSheet1)
    Set ws2 = Worksheets(shtSheet2)
[COLOR=#008000]’——————————–
‘CYCLE THROUGH COLUMNS[/COLOR]
    For Each Col In ws2.Columns(“A:E”)
[COLOR=#008000]’——————————–
‘CYCLE THROUGH ONLY USED CELLS IN COLUMN[/COLOR]
        LastRow = ActiveSheet.Cells(Rows.Count, Col.Column).End(xlUp).Row
        For Each Cell In Range(Cells(1, Col.Column), Cells(LastRow, Col.Column))
            If Not Cell = ws1.Cells(Cell.Row, Cell.Column).Value Then
               Cell.Interior.Color = vbYellow
               mydiffs = mydiffs + 1
            End If
        Next
    Next
[COLOR=#008000]’——————————–
‘DISPLAY A MESSAGE BOX TO DEMONSTRATE THE DIFFERENCES[/COLOR]
    MsgBox mydiffs & ” differences found”, vbInformation
    ActiveWorkbook.Sheets(shtSheet2).Select
End Sub

What if there was a cell on sheet1 with a value and the corresponding cell on sheet 2 is blank. Should that be highlighted?

Reply | Quote

Hi Maud
Thankyou and sorry for the delay in responding. The response is “Yes” this should be highlighted.

Reply | Quote