Hello -
I am trying to develop a Column in my spreadsheet that tracks the number of changes in the cells of another Column. Specifically in this example:
Column H triggers a formula in the cells of Column I (when H3 says "Yes", I3 populates with the date) - each time the value in I3 changes, I want J3's value to grow by 1. I want this to be possible whether the change is formula-driven or manual. I have the below VBA as a starting point, but it doesn't trigger properly when the first change is driven by a formula and it also begins populating the J cells with a 0 rather than a 1.
Option Explicit
Private Sub Worksheet_Change _
( _
ByVal Target As Range _
)
Const s_CheckColumn As String = "I:I"
Const s_CountColumn As String = "J:J"
If Intersect(Target, Range(s_CheckColumn)) Is Nothing Then Exit Sub
Dim rngCell As Range
For Each rngCell In Intersect(Target, Range(s_CheckColumn))
With Range(s_CountColumn).Cells(rngCell.Row)
.Value2 = IIf(.Value2 <> vbNullString, .Value2 + 1, IIf(rngCell.Value2 <> vbNullString, 0, vbNullString))
End With
Next rngCell
End Sub
Hi Patrick,
Your formula in Col I was referencing itself - you should have been given warnings by Excel about 'circular references' when you opened the file?
I've moved the logic/functionality of what you were doing with the formula in Col I to VBA.
Also, when you are using worksheet events you should turn off events in the event sub with Application.EnableEvents = False so that the changes you make with your own code don't trigger another event and force the code to run again.
Please see attached file for my VBA. Note that if the cell in Col H contains "Yes" and you click the cell and choose "Yes" again that triggers the event code and the count in Col J will increase.
Private Sub Worksheet_Change _
( _
ByVal Target As Range _
)
Const s_CheckColumn As String = "H:H"
Const s_CountColumn As String = "J:J"
If Intersect(Target, Range(s_CheckColumn)) Is Nothing Then Exit Sub
Application.EnableEvents = False
Dim rngCell As Range
Target.Offset(0, 1).Value = Now()
Target.Offset(0, 2).Value = Target.Offset(0, 2).Value + 1
Application.EnableEvents = True
End Sub
Regards
Phil
