VBA & Macro Problems

Change Excel Cell Color When Its Value Increases or Decreases

Tauseeq MagsiTauseeq Magsi Nov 14, 2026 868 views

Question details

Automatically change an Excel cell's fill color when a newly entered value is higher or lower than its previous value.

Change Excel Cell Color When Its Value Increases or Decreases
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user edits a cell and wants its fill to indicate whether the value increased or decreased.
Observed behavior
The desired result is a green fill for an increase and a red fill for a decrease.
Before you start

Choose the cell to monitor; the macro below uses A1. Test it on a copy of the workbook and make sure you can enable macros in your Excel environment.

Solution 1Recommended

Use a Worksheet_Change macro to compare values

Use this method to compare a single manually entered numeric value with its previous value automatically.

The macro uses Application.Undo to retrieve the previous value, then restores the new value and applies a fill. It ignores multi-cell edits and formulas entered into the monitored cell.

Running the macro disrupts normal Undo behavior. It does not track changes caused by formula recalculation, and selection may return to the edited cell.

1
Open the worksheet module

Right-click the worksheet tab containing the cell and select View Code.

2
Paste the macro

Paste this code into the worksheet's code window. Replace A1 with the cell you want to monitor if needed: Private Sub Worksheet_Change(ByVal Target As Range) Dim oldValue As Variant, newValue As Variant If Target.CountLarge <> 1 Then Exit Sub If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub If Target.HasFormula Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False newValue = Target.Value2 Application.Undo oldValue = Target.Value2 Target.Value2 = newValue If IsNumeric(oldValue) And IsNumeric(newValue) Then If Not IsEmpty(oldValue) And Not IsEmpty(newValue) Then If newValue > oldValue Then Target.Interior.Color = vbGreen ElseIf newValue < oldValue Then Target.Interior.Color = vbRed Else Target.Interior.ColorIndex = xlColorIndexNone End If End If End If CleanUp: Application.EnableEvents = True End Sub

3
Save and enable macros

Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Reopen the file and select Enable Content if prompted and you trust the workbook.

4
Test the cell

Enter a number in A1, then replace it with a higher or lower number. Check that the fill becomes green or red; equal numeric values clear the fill.

Use a Worksheet_Change macro to compare values
Undo and edit limitations: The comparison depends on Excel being able to undo the edit. If Undo is unavailable, the color may not update. Non-numeric values are not compared, and the macro does not process pasted ranges.
Free Microsoft Office alternative

Compare spreadsheet values in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and use conditional formatting to compare a current value with a separately recorded previous value. WPS does not fix the Excel VBA macro or its Undo limitations.

Use conditional formatting to highlight increases and decreases against a reference cell.Open and edit Microsoft Excel XLSX files for easier migration.Work with a familiar, easy-to-use spreadsheet interface.Use a free, lightweight office suite for everyday documents.
QA img-9

Frequently Asked Questions

Where do I paste the Worksheet_Change macro?

Right-click the relevant worksheet tab, select View Code, and paste the macro into that worksheet's code window rather than a standard module.

Why doesn't the macro work when I paste several cells?

The macro exits when more than one cell is changed at once. Edit the monitored cell individually, or use a different approach designed for multi-cell changes.

Can the macro detect changes caused by formulas?

No. Worksheet_Change does not run when a formula recalculates, and this example skips formulas entered into the monitored cell.

Why do I need to save the workbook as an XLSM file?

The XLSM format retains VBA macros. Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm), then enable trusted macros when prompted.

Can conditional formatting remember a cell's previous value?

Conditional formatting compares values available in the worksheet; it does not store a cell's previous value. Record the old value in another cell before changing the current value.