Worksheet change event

[VbaHell]

Hello all

Is it possible to use this worksheet change event for the whole column instead of the cell please

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address(False, False) = "I1" Then Range("J1").Value = Range("J1").Value + 1
End Sub

 

[Joe4]

Is it possible to use this worksheet change event for the whole column instead of the cell please

Yes, very easily. Let's say that you wanted to apply it to column "I". That is the 11th column, so you would use:

If Target.Column = 11 Then

 

[Peter_SSs]

Try this (also allows for multiple cell changes at the same time)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("I"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      c.Offset(, 1).Value = c.Offset(, 1).Value + 1
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[VbaHell]

Hi Joe & Peter
This works good but it has caused another issue now, I refresh the data which imports into my sheet and this causes the worksheet event to start the count.
I need the count to start only if the date actual changes, I can include a vlookup or calculation in a column next to the date if this solves the issue, any idea's please

 

[Joe4]

You would need to capture the previous value and compare it to the new value.

If you were manually updating the cells one at a time, you could do it using the method I describe in post 10 here: https://www.mrexcel.com/forum/excel...rmatting-negative-cell-value.html#post5136926

But, if you are importing/refreshing all of the data at once, you are not going to be able to do that. I think you would need to retain a copy of the data before the update, and compare the two lists.

 

[Peter_SSs]

I agree with Joe. You would have to duplicate column I in another column (perhaps hidden) or another worksheet (perhaps hidden).

Here is how you could do it using column Z to record the values.

First, manually copy the column I values to column Z.

Then update the worksheet_Change code to this

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("I"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If c.Value <> Cells(c.Row, "Z").Value Then
        c.Offset(, 1).Value = c.Offset(, 1).Value + 1
        Cells(c.Row, "Z").Value = c.Value
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

Now, if a value or group of values are entered into column I, only the changed ones should have their count increased in column J.

 

[VbaHell]

Hi Joe

I have done what you suggested and retained a copy of the data, I then complete a vlookup to match up the dates.
So in column "I" is the original date, column "J" the lookup date, if both dates match then fine but if there is a change then count +1
The bit I am stuck on is how to keep the change count and add to it if there is another change, can you please help me on this.

 

[Peter_SSs]

Have you tried the other suggestion?

 

[VbaHell]

sorry Peter, I did not see this, i will try now and let you know, thanks very much

 

[VbaHell]

Peter this is so good, it's great that the code updates the value is column "Z" to match column I so it can then track the changes again
I am amazed that you MVP's can think up these idea's
Thanks again