How to count the number of times a cell is changed in Excel?
To count the number of times a specified cell is changed in Excel, the VBA codes provided in this article can help.
The following VBA codes can help you to count the number of times a specified cell is changed in Excel.
1. In worksheet that contains one or more cells for which you need to calculate the total change, right-click the sheet tab, and then click View Code from the context menu. See screenshot:
2. In the opening Microsoft Visual Basic for Applications window, copy and paste one of the following VBA codes into the Code window according to your needs.
VBA code 1: Track changes to one cell only
Dim xCount As Integer Private Sub Worksheet_Change(ByVal Target As Range) Dim xRg As Range, xCell As Range On Error Resume Next If Target = Range("B9") Then xCount = xCount + 1 Range("C9").Value = xCount End If Application.EnableEvents = False Set xRg = Application.Intersect(Target.Dependents, Me.Range("B9")) If Not xRg Is Nothing Then xCount = xCount + 1 Range("C9").Value = xCount End If Application.EnableEvents = True End Sub
Note: In the code, B9 is the cell you need to count its changes, and C9 is the cell to populate the counting result. Please change them as you need.
VBA code 2: Track changes to multiple cells in a column
Private Sub Worksheet_Change(ByVal Target As Range) 'Updated by Extendoffice 20220916 Dim xSRg As Range Dim xRRg As Range Set xSRg = Range("B9:B1000") Set xCell = Intersect(xSRg, Target) If xCell Is Nothing Then Exit Sub Application.EnableEvents = False On Error Resume Next Set xCell = xCell.Range("A1") Set xRRg = xCell.Offset(0, 1) xRRg.Value = xRRg.Value + 1 Application.EnableEvents = True End Sub
Note: In this line "Set xRRg = xCell.Offset(0, 1)", the number 1 represents the number of columns to offset to the right of the starting reference (here the starting reference is column B, and the count you want to return is in column C which locates next to column B). If you need to output the results in column S, change the number 1 to 10.
From now on, when cell B9 or any cell in the range B9:B1000 changes, the total number of changes will be superimposed and automatically filled into the specified cell.