VBA & Macro Problems

How to Highlight Added, Changed, and Deleted Rows in Excel

Steven ParkerSteven Parker Nov 10, 2026 869 views

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.

How to Highlight Added, Changed, and Deleted Rows in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press ALT + F11 in Excel to open the Visual Basic for Applications (VBA) editor.

2
Insert a New Module

Click 'Insert' in the top menu bar, then select 'Module' to create a blank script window.

3
Paste the Comparison Code

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

4
Run the Macro

Close the VBA editor and return to Excel. Press ALT + F8 to open the Macro dialog box, select 'HighlightChanges', and click 'Run'.

Use a VBA Macro to Compare Worksheets and Highlight Rows
Sheet Names Requirement: The macro explicitly calls for worksheets named 'OldData' and 'NewData'. Make sure to rename your worksheet tabs to match these exactly, or update the VBA code with your actual sheet names.
Advanced Spreadsheet Macros

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. 1. Open Your Workbooks in WPS: Launch WPS Spreadsheet and open the file containing your old and new data.
  2. 2. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click on 'VBA Editor' (or press ALT + F11).
  3. 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.
Fully compatible with Microsoft Excel (.xlsx and .xlsm) macro-enabled formats.Built-in support for Visual Basic (VBA) to automate advanced data processing and row highlighting.Lightweight and optimized for speed, even when comparing thousands of rows of data.Familiar user interface ensuring a seamless migration from Microsoft Office.
microsoft office alternative - wps office

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.