Need to modify code
 

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 D9:D374, 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("D9:D374"), .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

Need to modify code
 

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("D9:D374"), .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." wrote in message
...
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 D9:D374, 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("D9:D374"), .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

Need to modify code
 

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

Any ideas?

"Harald Staff" wrote:

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("D9:D374"), .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." wrote in message
...
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 D9:D374, 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("D9:D374"), .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

Need to modify code
 

This line has an extra dot
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
change it to
If Not Intersect(Range("D9:D374"), Cel) Is Nothing Then
--
HTH...

Jim Thomlinson


"Greg H." wrote:

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

Any ideas?

"Harald Staff" wrote:

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("D9:D374"), .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." wrote in message
...
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 D9:D374, 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("D9:D374"), .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

Need to modify code
 

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.

"Jim Thomlinson" wrote:

This line has an extra dot
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
change it to
If Not Intersect(Range("D9:D374"), Cel) Is Nothing Then
--
HTH...

Jim Thomlinson


"Greg H." wrote:

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

Any ideas?

"Harald Staff" wrote:

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("D9:D374"), .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." wrote in message
...
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 D9:D374, 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("D9:D374"), .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

Need to modify code
 

Any help on the below question?

"Greg H." wrote:

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.

"Jim Thomlinson" wrote:

This line has an extra dot
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
change it to
If Not Intersect(Range("D9:D374"), Cel) Is Nothing Then
--
HTH...

Jim Thomlinson


"Greg H." wrote:

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

Any ideas?

"Harald Staff" wrote:

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("D9:D374"), .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." wrote in message
...
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 D9:D374, 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("D9:D374"), .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

Need to modify code
 

What part are you having trouble with?

Post your current code. It may help.

Greg H. wrote:

Any help on the below question?

"Greg H." wrote:

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.

"Jim Thomlinson" wrote:

This line has an extra dot
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
change it to
If Not Intersect(Range("D9:D374"), Cel) Is Nothing Then
--
HTH...

Jim Thomlinson


"Greg H." wrote:

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

Any ideas?

"Harald Staff" wrote:

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("D9:D374"), .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." wrote in message
...
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 D9:D374, 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("D9:D374"), .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

Need to modify code
 

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("D9:D374"), 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("D9:D374"), .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" wrote:

What part are you having trouble with?

Post your current code. It may help.

Greg H. wrote:

Any help on the below question?

"Greg H." wrote:

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.

"Jim Thomlinson" wrote:

This line has an extra dot
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
change it to
If Not Intersect(Range("D9:D374"), Cel) Is Nothing Then
--
HTH...

Jim Thomlinson


"Greg H." wrote:

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

Any ideas?

"Harald Staff" wrote:

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("D9:D374"), .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." wrote in message
...
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 D9:D374, 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("D9:D374"), .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

Need to modify code
 

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

Greg H. wrote:

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("D9:D374"), 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("D9:D374"), .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" wrote:

What part are you having trouble with?

Post your current code. It may help.

Greg H. wrote:

Any help on the below question?

"Greg H." wrote:

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.

"Jim Thomlinson" wrote:

This line has an extra dot
If Not Intersect(Range("D9:D374"), .Cel) Is Nothing Then
change it to
If Not Intersect(Range("D9:D374"), Cel) Is Nothing Then
--
HTH...

Jim Thomlinson


"Greg H." wrote:

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

Any ideas?

"Harald Staff" wrote:

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("D9:D374"), .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." wrote in message
...
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 D9:D374, 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("D9:D374"), .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


--

Dave Peterson