Change Excel Cell Color When Its Value Increases or Decreases
Question details
Automatically change an Excel cell's fill color when a newly entered value is higher or lower than its previous value.

- 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.
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.
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.
Right-click the worksheet tab containing the cell and select View Code.
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
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.
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.

Compare against a recorded value with conditional formatting
Use this macro-free alternative when you can record the previous value in a separate cell before each change.
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.

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.




