Compare rows (and cells within those rows) on two spreadsheets using VBA in Excel, highlight differences

Question

I am trying to take two sheets in a workbook and highlight cells in Sheet2 that are different from Sheet1. The sheets can range from a small number of rows to hundreds of them. I will be happy to answer any questions, I have never used VBA before, but I have experience in other languages.

It needs to go by Sheet2's rows, then cells within the current row. Take the first cell in the row and see if the contents of that cell exists within Sheet1, if that cell's contents don't exist, highlight the whole row as a new entry. If the contents do appear in Sheet1, go through each cell of the row that entry appears on in each sheet and highlight changes on only Sheet2.

All I have figured out so far:

Sub DetectChanges()
Rem compares two sheets by row. let sheet1 be the old one and sheet2 be the new one.
Rem *hopefully* highlights any differences. Make column1 unique identifiers
Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
For Each rw In ws2.Rows

' check identifier for active row & detect changes
Next

End Sub

Thank you for any help!

user3598756 user3598756 · Accepted Answer · 2016-09-23T15:43:54

edited after OP's clarifications

this (commented) code should get you on the right way:

Option Explicit

Sub DetectChanges()
    Dim ws1 As Worksheet, ws2 As Worksheet '<-- explicitly declare each variable type
    Dim ws1Data As Range, f As Range, cell As Range
    Dim icol As Long

    Set ws1Data = Worksheets("Sheet01").Columns(1).SpecialCells(xlCellTypeConstants) '<-- set a range with Sheet1 cells containing data

    With Worksheets("Sheet02") '<--| reference Sheet2
        For Each cell In Intersect(.UsedRange, .Columns(1)).SpecialCells(xlCellTypeConstants) '<-_| loop through its column "A" non blank cells
            Set f = ws1Data.Find(what:=cell.value, LookIn:=xlValues, LookAt:=xlWhole) '<--| search for current cell value in Sheet1 data
            If f Is Nothing Then '<--| if not found then...
                Intersect(cell.EntireRow, .UsedRange).Interior.ColorIndex = 3 '<--| highlight current cell entire row
            Else
                For icol = 1 To .Range(cell, .Cells(cell.Row, .Columns.Count).End(xlToLeft)).Columns.Count - 1 '<--| loop through Sheet2 current cell row
                    If f.Offset(, icol) <> cell.Offset(, icol) Then '<--| if it doesn't match corresponding cell in Sheet1
                        cell.Offset(, icol).Interior.ColorIndex = 3 '<--| highlight Sheet2 not-matching cell
                        f.Offset(, icol).Interior.ColorIndex = 3 '<--| highlight Sheet1 not-matching cell
                    End If
                Next icol
            End If
        Next cell
    End With
End Sub

Compare rows (and cells within those rows) on two spreadsheets using VBA in Excel, highlight differences

1 Answers