Spreadsheet Compare Highlight Function

Attached is a suggested solution in VBA. Maybe it will help you in your project.

Sub HighlightChanges()
    Dim wsOld As Worksheet, wsNew As Worksheet
    Dim oldData As Range, newData As Range
    Dim cell As Range, matchCell As Range
    
    ' Set sheets
    Set wsOld = ThisWorkbook.Sheets("OldData")
    Set wsNew = ThisWorkbook.Sheets("NewData")
    
    ' Define data ranges
    Set oldData = wsOld.UsedRange
    Set newData = wsNew.UsedRange
    
    ' Loop through new data to find added or changed rows
    For Each cell In newData.Columns(1).Cells
        Set matchCell = oldData.Columns(1).Find(cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
        If matchCell Is Nothing Then
            ' Added row (green)
            cell.EntireRow.Interior.Color = RGB(144, 238, 144) ' Light green
        ElseIf Not CompareRows(cell.EntireRow, matchCell.EntireRow) Then
            ' Changed row (yellow)
            cell.EntireRow.Interior.Color = RGB(255, 255, 102) ' Yellow
        End If
    Next cell
    
    ' Loop through old data to find deleted rows
    For Each cell In oldData.Columns(1).Cells
        Set matchCell = newData.Columns(1).Find(cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
        If matchCell Is Nothing Then
            ' Deleted row (red)
            cell.EntireRow.Interior.Color = RGB(255, 102, 102) ' Light red
        End If
    Next cell
End Sub

Function CompareRows(row1 As Range, row2 As Range) As Boolean
    Dim i As Integer
    For i = 1 To row1.Columns.Count
        If row1.Cells(1, i).Value <> row2.Cells(1, i).Value Then
            CompareRows = False
            Exit Function
        End If
    Next i
    CompareRows = True
End Function

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.