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.

Count the number of times a cell is changed with VBA code

Count the number of times a cell is changed with VBA code

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.

Unlock Excel Magic with Kutools AI

• Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
• Custom Formulas: Generate tailored formulas to streamline your workflows.
• VBA Coding: Write and implement VBA code effortlessly.
• Formula Interpretation: Understand complex formulas with ease.
• Text Translation: Break language barriers within your spreadsheets.

Enhance your Excel capabilities with AI-powered tools. Download Now and experience efficiency like never before!