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.
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
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! (Full-Featured 30-Day Free Trial)