Per,

Your point is very valid. (See the code in this thread to Dave Peterson)

Thank you!

"Per Jessen" wrote:

Hi EagleOne

As Target is already a range you can just use Set MyRange =Target, or just
juse Target in your macro.

From what you post I can not tell if IndexColumn is supposed to be a fixed
value or subject to change. As your code verify that Target is just one
cell, you do not need a For Each...Next loop.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
Application.EnableEvents = False
With Cells(Target.Row, IndexColumn+1)
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now()
End With
Application.EnableEvents = True
End If
End Sub

Regards,
Per

skrev i meddelelsen
.. .
I thing I have it. The following works in that I am limiting the Date/Time
to all changes where the
cell count is 1.

Anyone know where this is not appropriate or have a better approach?

Set myRange = Range(Target.Address)

If Target.Count = 1 Then
For Each myCell In myRange
With Me.Cells(myCell.Row, IndexColumn + 1)
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = myTime
End With
Next myCell
End If

TIA EagleOne

wrote:

Hi,

maybe this

If Target.Column < 125 Then Exit Sub

column 125 is "DU" so change to suit.

Mike

On Wed, 14 Oct 2009 08:22:50 -0400,
wrote:

2003 2007

What would the VBA Syntax to eliminate (from ChangeEvent) i.e. the
addition of a Column within the
Target Range? The issue is that all cells in that column would be
populated eith a time/date stamp.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'
'
Dim myCell As Range
Dim myRange As Range
Dim myTime As String

myTime = Now()
Set myRange = Range(Target.Address)
With Application
.EnableEvents = False
.Screenupdating = False
End with
For Each myCell In myRange
With Me.Cells(myCell.Row, "DZ")
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = myTime
End With
Next myCell
Range("DZ1").Value = "Date Last Update"
With Application
.ScreenUpdating = True
.EnableEvents = True
End with
End Sub


TIA EagleOne