Note: The other languages of the website are Google-translated. Back to English

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: 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.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (49)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
HAI ,



THE ABOVE VBS ONLY C2 MOVE TO D2,



INEED C2 TO C55 MOVE TO D2 TO D55



CAN YOU HELP AND SEND TO ME alexmathew33@gmail.com
This comment was minimized by the moderator on the site
Please help me on the below scenario:
From Sheet 1:
A1=VALUE (Changes due to RTD with Formula)
B2= VALUE1 (Changes due to RTD with Formula)

Copy all previous values cells A1,B1 into Sheet2 of columns M,NOF Same excel or Sheet1 of New Workbook

Please share with me to melukotahari@gmail.com

-Melukota
This comment was minimized by the moderator on the site
CAN WE GET THE VBS FOR THE ABOVE
This comment was minimized by the moderator on the site
What if cell C2 is a formula? How do I record the values of C2 if it is a formula?
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
HAI ,



THE ABOVE VBS ONLY C2 MOVE TO D2,



INEED C2 TO C55 MOVE TO D2 TO D55



CAN YOU HELP AND SEND TO ME alexmathew33@gmail.com
This comment was minimized by the moderator on the site
Hi--I really appreciate this tutorial...I'm trying to record the changed value on a different spreadsheet. For example, I want to record the value of sheet1 C2 on sheet2 D2. Can you provide adjusted code?
Thank you!
This comment was minimized by the moderator on the site
Can the records be in Horizontal instead of Vertical?
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
Hi,
Please try the method in this article:
How to remember or save previous cell value of a changed cell in Excel?
https://www.extendoffice.com/documents/excel/5056-excel-remember-save-previous-cell-value.html
This comment was minimized by the moderator on the site
thank you for this but instead of going on forever how could i restart back at first cell after X amount of times?
This comment was minimized by the moderator on the site
Hi,
Do you mean after recording X mount of times, you want to restart back to the first record value?
Sorry I am not sure I got your question. Would be nice if you could provide screenshot of what you are trying to do.
This comment was minimized by the moderator on the site
Thanks

but what can i do if i need to repeat it for many cells
This comment was minimized by the moderator on the site
Thanks

but what can i do if i need to repeat it for a raw
This comment was minimized by the moderator on the site
Peki bu kaydı yatay olarak nasıl kaydedeceğiz. Satırlara değil Sütunlara kaydetmesini istiyorum. Teşekkürler
This comment was minimized by the moderator on the site
How do I save the number as soon as it is generated not after a new number is generated? The problem I am having is the number is not recorded right away but after a second number is created; this means neither the cell that I am recording or the cell that is the target have the number...how can I record as soon as the number is generated? Thanks for your help!
This comment was minimized by the moderator on the site
Hi John,
Sorry can't help you with that. Welcome to post any question in our forum: https://www.extendoffice.com/forum.html. Thank you for your comment.
This comment was minimized by the moderator on the site
Hello, Would it be possible to apply this for more than one cell ?
This comment was minimized by the moderator on the site
Hi! Thanks for the code, but I have a question

Is there a way to modify the code, such that it records the value if the cell daily, or on certain days, which I specify in a separate column?


Thanks!
This comment was minimized by the moderator on the site
Try This

Dim xVal As String
Dim iVal As Integer
Private Sub Worksheet_Change(ByVal Target As Range)
Static xCount As Integer
iVal = Application.WorksheetFunction.Count(Range("F:F"), 1)
xCount = iVal
Application.EnableEvents = False
If Target.Address = Range("C2").Address Then
Range("E3").Offset(xCount, 0).Value = Range("C2").Value
Range("F3").Offset(xCount, 0).Value = Now
xCount = xCount + 1
Else
If xVal <> Range("C2").Value Then
Range("E3").Offset(xCount, 0).Value = Range("C2").Value
Range("F3").Offset(xCount, 0).Value = Now
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
This comment was minimized by the moderator on the site
I tried this code for the C2 cell which contatins DDE values which changes second by second. I use this following code but not working.

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
This comment was minimized by the moderator on the site
Good day,
Which Excel version do you use?
This comment was minimized by the moderator on the site
it works when I type in the data, it doesn't work when I stream real time data to this cell directly (=RTD(.....)). how can I make it work with RTD ?
This comment was minimized by the moderator on the site
Thanks very much for the tutorials, I like to know if there is a way to make just a cell behave as a normal calculator.
i.e this cell should be capable of summing figures that appears in another cell, while keeping last cumulative figure visible.
This other cell will be the key-in cell or active cell.
Example:
Cell 1: =2*5, Answer appears in Cell 2,
Cell 1: =3*6.8, Answer is added to the previous value resulting from (2*5) and still appears in cell 2.
This comment was minimized by the moderator on the site
The formula above doesn't work for formulas, only for manual input. is there any way to change the coding to make it work for cells which contain formula?



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
This comment was minimized by the moderator on the site
Good Day,
Please try the below VBA.



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
This comment was minimized by the moderator on the site
doesn't work for formulas
This comment was minimized by the moderator on the site
This world fine, however, I ned to apply this code to 2 different cells, saving the changing values for each cell in separate, corresponding columns. How can the code be modified? Thanks.
This comment was minimized by the moderator on the site
Hi,

Please try the method in this article:

How to remember or save previous cell value of a changed cell in Excel?

https://www.extendoffice.com/documents/excel/5056-excel-remember-save-previous-cell-value.html
This comment was minimized by the moderator on the site
Is it possible to adapt this and use with DDE/RTD? Works fine when manually changing the cells, but not with DDE/RTD.
This comment was minimized by the moderator on the site
Can this be changed to work for multiple cells in one worksheet?
This comment was minimized by the moderator on the site
Hi,

Please try the method in this article:

How to remember or save previous cell value of a changed cell in Excel?

https://www.extendoffice.com/documents/excel/5056-excel-remember-save-previous-cell-value.html
This comment was minimized by the moderator on the site
Hello! I tried using this code to record every change in the value of a particular cell. However, I was wondering if anyone could help me by modifying it so the change in value is collected in a DIFFERENT tab and also so it is saved every time the workbook is closed. Since it sort of re-sets itself each time the workbook is opened without saving the previous values. Code: 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("J7").Address Then
Range("AB2").Offset(xCount, 0).Value = xVal
xCount = xCount + 1
Else
If xVal <> Range("J7").Value Then
Range("AB2").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("J7").Value
End Sub
This comment was minimized by the moderator on the site
Hi, Thanks for the below. Quick question....are you able to reset this at times so that on your request, you can get the macro to delete all previous numbers and start recording numbers again from cell D2? At the moment, numbers are recorded D2, D3, D4, D5, D6 etc
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL