How to Highlight Added, Changed, and Deleted Rows in Excel
Question details
The user wants to compare two Excel worksheets and apply specific row background colors based on whether the data rows are new, modified, or deleted.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing two versions of a dataset to identify modifications, additions, and deletions across the sheets.
- Observed behavior
- A programmatic or automated way is needed to apply green to added rows, yellow to changed rows, and red to deleted rows, as built-in tools like Spreadsheet Compare do not offer three-color row highlighting.
Ensure both the old and new datasets have a unique identifier (key column) in the first column, and save your workbook as a Macro-Enabled Workbook (.xlsm) before running the VBA code.
Use a VBA Macro to Compare Worksheets and Highlight Rows
Since Excel's built-in Spreadsheet Compare tool doesn't support a three-color row-highlighting workflow natively, a VBA macro is the most effective way to compare a key column and apply color-codes for added, changed, and deleted rows.
This macro assumes your previous data is in a sheet named 'OldData' and your updated data is in a sheet named 'NewData'. It uses exact matching on the first column to identify and highlight differences.
Press ALT + F11 in Excel to open the Visual Basic for Applications (VBA) editor.
Click 'Insert' in the top menu bar, then select 'Module' to create a blank script window.
Copy and paste the following VBA code into the module window: Sub HighlightChanges() Dim wsOld As Worksheet, wsNew As Worksheet Dim oldData As Range, newData As Range Dim cell As Range, matchCell As Range Set wsOld = ThisWorkbook.Sheets("OldData") Set wsNew = ThisWorkbook.Sheets("NewData") Set oldData = wsOld.UsedRange Set newData = wsNew.UsedRange 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 cell.EntireRow.Interior.Color = RGB(144, 238, 144) ElseIf Not CompareRows(cell.EntireRow, matchCell.EntireRow) Then cell.EntireRow.Interior.Color = RGB(255, 255, 102) End If Next cell 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 cell.EntireRow.Interior.Color = RGB(255, 102, 102) 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
Close the VBA editor and return to Excel. Press ALT + F8 to open the Macro dialog box, select 'HighlightChanges', and click 'Run'.

Compare and Highlight Data Differences with WPS Spreadsheet
WPS Spreadsheet natively supports VBA macros, allowing you to seamlessly run comparison scripts to highlight added, changed, and deleted rows. Manage your complex data tasks effortlessly in a highly compatible, easy-to-use interface.
- 1. Open Your Workbooks in WPS: Launch WPS Spreadsheet and open the file containing your old and new data.
- 2. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click on 'VBA Editor' (or press ALT + F11).
- 3. Insert and Run the Code: Go to 'Insert' > 'Module', paste the dataset comparison macro into the editor, and run it to instantly color-code your rows.

Frequently Asked Questions
Why is the VBA macro returning a 'Subscript out of range' error?
This usually happens if the worksheet names in your workbook do not exactly match the names defined in the script ('OldData' and 'NewData'). You must either rename your tabs in the spreadsheet to match or modify the Set wsOld and Set wsNew lines in the code to reflect your current sheet names.
Can I use Excel's built-in Conditional Formatting to compare rows instead of VBA?
Conditional Formatting can highlight simple differences or duplicates between two specific columns, but it struggles with complex scenarios like identifying completely deleted rows across two separate sheets. A VBA macro is heavily recommended for a robust three-color highlighting workflow.
How do I change the highlight colors in the VBA script?
You can adjust the colors by modifying the RGB values inside the VBA script. By default, RGB(144, 238, 144) is light green, RGB(255, 255, 102) is yellow, and RGB(255, 102, 102) is red. Simply replace these numbers with the RGB codes of your preferred colors.




