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.
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.
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/5/15 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 End If Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = Range("C2").Address Then xVal = Target.Value End If End Sub
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.
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
200 New Features for Excel, Make Excel Much Easy and Powerful: