Need to modify code

[Greg H.]

I am using the following code to track when i make a change to a cell value.
It works great but i need to make a modification i dont know how to do. If i
type a value in any cell in the range of D9374, the date gets populated.
If i copy and paste a value in to that range, the date is not populated. I
need it to be able to work with the copy/paste. Any help would be greatly
appreciated.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("D9374"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End With
End Sub

 

[Harald Staff]

Hi

When pasting then target may be multiple cells. Your code exits if
target.count is >1.

Try something like

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cel as Range
For each Cel in Target
If Not Intersect(Range("D9374"), .Cel) Is Nothing Then
Application.EnableEvents = False
With Cel.Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Next

HTH. Best wishes Harald

 

[Greg H.]

When i try this i get the error Compile Error: Invalid or unqualified
reference.

Any ideas?

 

[Jim Thomlinson]

This line has an extra dot
If Not Intersect(Range("D9374"), .Cel) Is Nothing Then
change it to
If Not Intersect(Range("D9374"), Cel) Is Nothing Then

 

[Greg H.]

Good catch ... now one more request, can this new code be combined to update
regardless if you copy/paste or manually type a value. Right now with the
new code it only updates if it is copy/paste.

Thanks for being patient with me.

 

[Greg H.]

Any help on the below question?

Good catch ... now one more request, can this new code be combined to update
regardless if you copy/paste or manually type a value. Right now with the
new code it only updates if it is copy/paste.

Thanks for being patient with me.

 

[Dave Peterson]

What part are you having trouble with?

Post your current code. It may help.

 

[Greg H.]

Code 1 updates when you copy vales from one cell to another but i want to be
able to combine that with Code 2 so that if you copy or enter manually it
gets updated. I can not get them to work together.

CODE 1
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cel as Range
For each Cel in Target
If Not Intersect(Range("D9374"), Cel) Is Nothing Then
Application.EnableEvents = False
With Cel.Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Next
End Sub

CODE 2
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("D9374"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End With
End Sub

 

[Dave Peterson]

First, you only get one worksheet_Change event per worksheet.


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim Cel As Range
Dim RngToInspect As Range
Dim myIntersect As Range

Set RngToInspect = Me.Range("d9:d374")

Set myIntersect = Intersect(Target, RngToInspect)

If myIntersect Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
For Each Cel In myIntersect.Cells
With Cel.Offset(0, 9)
'.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
Next Cel
Application.EnableEvents = True
End Sub