VBA Question - Change Event Triggering

[Ghost00]

Hi,

I've tried this for several hours before registering into the forums, I looked and experimented a lot, but I guess this is just beyond me.

I have zero prior experience with VBA or programming.

All I'm trying to do is to log the time when the contents of a Cell is changed. I've managed to do that by looking around the Web (this is the first time messing with VBA). Since the example I found was only for a single range, I added other "IFs" by myself.

All ranges are working and logging the time when they are changed. The problem is that when I delete the contents of the cells it triggers some king of cyclic event updating every other cell in the "vertical" axis of the spreadsheet.

I've managed to figure out that what I should be looking for seems to be a way to avoid triggering a change event when what I'm doing is deleting the contents of the cells, I've read and read, but I guess VBA language is just beyond me at this moment. So please don't think I'm lazy for asking the exact code, if you can provide me.

Summary: What I would like to do is to avoid triggering a Change Event when I delete the contents of any of the cells within these ranges. Sorry about my English.

This is what I'm using so far:

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("E3:E1000")) Is Nothing Then
Target.Offset(0, 1) = Now()
End If


If Not Intersect(Target, Range("G3:G1000")) Is Nothing Then
Target.Offset(0, 1) = Now()
End If


If Not Intersect(Target, Range("i3:i1000")) Is Nothing Then
Target.Offset(0, 1) = Now()
End If

If Not Intersect(Target, Range("k3:k1000")) Is Nothing Then
Target.Offset(0, 1) = Now()
End If

If Intersect(Target, Range("m3:m1000")) Is Nothing Then
Target.Offset(0, 1) = Now()
End If


End Sub

 

[DanteAmor]

Hi and welcome to the board!

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  If Not Intersect(Target, Range("E3:E1000, G3:G1000, I3:I1000, K3:K1000, M3:M1000")) Is Nothing Then
    Target.Offset(0, 1) = Now()
  End If
End Sub

 

[Ghost00]

Hi and welcome to the board!

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  If Not Intersect(Target, Range("E3:E1000, G3:G1000, I3:I1000, K3:K1000, M3:M1000")) Is Nothing Then
    Target.Offset(0, 1) = Now()
  End If
End Sub

Hey My Friend, apparently it worked like a charm!

Thank you so much Dante!

Would you point out any study material for beginners? Books are my preference.

Best Regards!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

Review this links