Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or
0
0
0
s2smodern

How to record changing values in a cell in Excel?

How to record every changing value for a frequently changing cell in Excel? For example, the original value in cell C2 is 100, when changing number 100 to 200, the original value 100 will be displayed in cell D2 automatically for recording. Go ahead to change 200 to 300, number 200 will be inserted into cell D3, change 300 to 400 will display 300 to D4 and so on. The method in this article can help you to achieve it.

Record changing values in a cell with VBA code


Record changing values in a cell with VBA code


The below VBA code can help you record every changing value in a cell in Excel. Please do as follows.

1. In the worksheet contains the cell you want to record changing values, right click the sheet tab and then click View Code from the context menu. See screenshot:

2. Then the Microsoft Visual Basic for Applications window is opening, please copy below VBA code into the Code window.

VBA code: record changing values in a cell

Dim xVal As String
'Update by Extendoffice 2018/8/22
Private Sub Worksheet_Change(ByVal Target As Range)
    Static xCount As Integer
    Application.EnableEvents = False
    If Target.Address = Range("C2").Address Then
        Range("D2").Offset(xCount, 0).Value = xVal
        xCount = xCount + 1
    Else
        If xVal <> Range("C2").Value Then
         Range("D2").Offset(xCount, 0).Value = xVal
        xCount = xCount + 1
        End If
    End If
    Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    xVal = Range("C2").Value
End Sub

Notes:

1. In the code, C2 is the cell you want to record all its changing values. D2 is the cell you will populate the first changing value of C2.

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

From now on, every time when you change values in cell C2, the previous changing values will be recorded in D2 and the cells below D2.


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
People in conversation:
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Maybe · 1 months ago
    Hello, Would it be possible for this macro to record two seperate cells in two seperate columns? Ie. Can I record All values from A1 in Column B and all Values of C1 in column D?
  • To post as a guest, your comment is unpublished.
    Xy · 1 months ago
    What if cell C2 is a formula? How do I record the values of C2 if it is a formula?
    • To post as a guest, your comment is unpublished.
      crystal · 1 months ago
      Good Day,
      The code has been optimized. Please have a try and thanks for your comment.

      Dim xVal As String
      Private Sub Worksheet_Change(ByVal Target As Range)
      Static xCount As Integer
      Application.EnableEvents = False
      If Target.Address = Range("C2").Address Then
      Range("D2").Offset(xCount, 0).Value = xVal
      xCount = xCount + 1
      Else
      If xVal <> Range("C2").Value Then
      Range("D2").Offset(xCount, 0).Value = xVal
      xCount = xCount + 1
      End If
      End If
      Application.EnableEvents = True
      End Sub
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      xVal = Range("C2").Value
      End Sub
  • To post as a guest, your comment is unpublished.
    Jorge Jaramillo · 2 months ago
    Hi


    This works really well if the value in C2 is entered each time, but it doesn't work if C2 contains a formula. Is there a way to this same thing but with a formula in C2?


    Thanks for this easy solution.
    • To post as a guest, your comment is unpublished.
      Andrew K · 2 months ago
      If you find out how to use it if C2 contains a formula will you please please let me know how you did it. I can't seem to find how to anywhere on the internet.
      • To post as a guest, your comment is unpublished.
        crystal · 1 months ago
        Good Day,
        The code has been optimized. Please have a try and thanks for your comment.

        Dim xVal As String
        Private Sub Worksheet_Change(ByVal Target As Range)
        Static xCount As Integer
        Application.EnableEvents = False
        If Target.Address = Range("C2").Address Then
        Range("D2").Offset(xCount, 0).Value = xVal
        xCount = xCount + 1
        Else
        If xVal <> Range("C2").Value Then
        Range("D2").Offset(xCount, 0).Value = xVal
        xCount = xCount + 1
        End If
        End If
        Application.EnableEvents = True
        End Sub
        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        xVal = Range("C2").Value
        End Sub