[VBA] How to keep record of value changing 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.