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. When you change 200 to 300, the 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 containing the cell where 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 SubNotes: 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 values will be recorded in D2 and the cells below it.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite ā Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license ā set up in minutes (MSI-ready)
- Works better together ā streamlined productivity across Office apps
- 30-day full-featured trial ā no registration, no credit card
- Best value ā save vs buying individual add-in