[Excel VBA] How to keep record of value change in specific cell?

In this post, I will go through how to keep record of specific cell's value change. In this way, you can keep record or log of changes in specific cell. Notice that all screen shots are captured from Korean version of Excel 2010.

Ideally this task should done by Database with several automation job but, for sure, you can do this by Excel VBA functionality. Why VBA? Because we have to catch event of value change in cell.

Ok, just follow 3 easy steps as below.

1. Create new Excel file with xlsm type. Which is Excel Macro Enabled document type. And then rename sheet1, sheet2 to "원본" "기록". If you change this sheet's name, you have to change in VBA code, too.

2. Right click on "원본" sheet, which is originally sheet1. VBA code will catch event if 원본 sheet's A1 cell value is changed. Select "View Code" or press Alt+F11 and then select "원본" sheet.

3. VBA Editor will open and you can safely copy below code into Sheet1(원본) sheet in editor.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
With Sheets("기록")
lr = .Cells(Rows.Count, "A").End(xlUp).Row
If Range("A1").Value <> .Cells(lr, "A") Then
.Cells(lr + 1, "A").Value = Range("A1").Value
.Cells(lr + 1, "B").Value = Now
End If
End With
End If
End Sub

* Caution!

If cell value changed automatically, use below code.

Private Sub Worksheet_Calculate()
With Sheets("기록")
lr = .Cells(Rows.count, "A").End(xlUp).Row
If Range("A1").Value <> .Cells(lr, "A") Then
.Cells(lr + 1, "A").Value = Range("A1").Value
.Cells(lr + 1, "B").Value = Now
End If
End With
End Sub

We're done! If you change value in "원본" sheet's A1 cell, all changes will be logged in "기록" sheet's A column. Additionally, exact time the changes made will be logged into column B.

Hope this helps.