How do I get the old value of a changed cell in Excel VBA?

calendar_today Asked Jan 12, 2011
thumb_up 27 upvotes
history Updated April 16, 2026

Question posted 2011 · +20 upvotes

I’m detecting changes in the values of certain cells in an Excel spreadsheet like this…

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim old_value As String
Dim new_value As String

For Each cell In Target

    If Not (Intersect(cell, Range("cell_of_interest")) Is Nothing) Then
        new_value = cell.Value
        old_value = ' what here?
        Call DoFoo (old_value, new_value)
    End If

Next cell

End Sub

Assuming this isn’t too bad a way of coding this, how do I get the value of the cell before the change?

Accepted answer +27 upvotes

try this

declare a variable say

Dim oval

and in the SelectionChange Event

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
oval = Target.Value
End Sub

and in your Worksheet_Change event set

old_value = oval

3 code variants in this answer

  • Variant 1 — 1 lines, starts with Dim oval
  • Variant 2 — 3 lines, starts with Public Sub Worksheet_SelectionChange(ByVal Target As Range)
  • Variant 3 — 1 lines, starts with old_value = oval

Excel VBA objects referenced (4)

  • Range — Refer to Cells by Using a Range Object
  • Range — Delete Duplicate Entries in a Range
  • Target.Value — Cell Error Values
  • Target.Value — Fill a Value Down into Blank Cells in a Column

Top excel-vba Q&A (6)

+27 upvotes ranks this answer #12 out of 136 excel-vba solutions on this site — top 9%.
excel-vba