Forum Discussion

OrionF's avatar
OrionF
Copper Contributor
Jan 17, 2025

Spreadsheet Compare Highlight Function

Hello

I would like to know if rows can be highlighted in the compared files using the Spreadsheet Compare program. I want the rows changed, rows deleted and the rows with entered values changed highlighted in different colors respectively on the compared sheets by the Spreadsheet Compare program itself. Is it possible?

Basically, the Spreadsheet Compare program takes in two spreadsheets - Old Data and New Data. I want the added rows detected in the new data sheet to be highlighted green, the changed rows should be highlighted yellow in the new data sheet and the deleted rows should be highlighted red in the old data sheet. I would like to know if there's any way  to accomplish this using Spreadsheet Compare or any external method.

I'd greatly appreciate any help!

Thanks!

excel
Formulas and Functions
Macros and VBA
Office Scripts

3 Replies

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Jan 20, 2025

    https://answers.microsoft.com/en-us/msoffice/forum/all/vba-code-to-compare-two-columns-in-two-sheets-and/40b5c866-c752-4f5d-87e0-6ac03920b402

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jan 19, 2025

    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.

Resources