I've used this Macro and need to tweak it:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("a10"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("b10")
.NumberFormat = "dd mmm yyyy"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub

This macro stamps B10 if something is entered in A10 but I also want this to
continue to row 1000. Any help would be appreciated

Try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .cells.Count 1 Then
Exit Sub
end if

If Intersect(me.Range("a10:1000"), .Cells) Is Nothing Then
'do nothing
else
Application.EnableEvents = False
With .offset(0,1)
.NumberFormat = "dd mmm yyyy"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub


I made some completely arbitrary changes (a block if/then instead of the single
line if/then). And I changed the "if .. is nothing" to something I find easier
to understand <vbg.

If you want, you can change back. It shouldn't be too difficult.


JeffK wrote:

I've used this Macro and need to tweak it:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("a10"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("b10")
.NumberFormat = "dd mmm yyyy"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub

This macro stamps B10 if something is entered in A10 but I also want this to
continue to row 1000. Any help would be appreciated

--

Dave Peterson

I pasted your code and the following error occured:

Method 'Range' of object'_Worksheet'failed

and highlighted

If Intersect(Me.Range("a10:1000"), .Cells) Is Nothing Then


"Dave Peterson" wrote:

Try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .cells.Count 1 Then
Exit Sub
end if

If Intersect(me.Range("a10:1000"), .Cells) Is Nothing Then
'do nothing
else
Application.EnableEvents = False
With .offset(0,1)
.NumberFormat = "dd mmm yyyy"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub


I made some completely arbitrary changes (a block if/then instead of the single
line if/then). And I changed the "if .. is nothing" to something I find easier
to understand <vbg.

If you want, you can change back. It shouldn't be too difficult.


JeffK wrote:

I've used this Macro and need to tweak it:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("a10"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("b10")
.NumberFormat = "dd mmm yyyy"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub

This macro stamps B10 if something is entered in A10 but I also want this to
continue to row 1000. Any help would be appreciated

--

Dave Peterson
.

Never mind Dave I discovered the solution (a10:a1000)

Thanks for the help, you're always a great resource.

"Dave Peterson" wrote:

Try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .cells.Count 1 Then
Exit Sub
end if

If Intersect(me.Range("a10:1000"), .Cells) Is Nothing Then
'do nothing
else
Application.EnableEvents = False
With .offset(0,1)
.NumberFormat = "dd mmm yyyy"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub


I made some completely arbitrary changes (a block if/then instead of the single
line if/then). And I changed the "if .. is nothing" to something I find easier
to understand <vbg.

If you want, you can change back. It shouldn't be too difficult.


JeffK wrote:

I've used this Macro and need to tweak it:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("a10"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("b10")
.NumberFormat = "dd mmm yyyy"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub

This macro stamps B10 if something is entered in A10 but I also want this to
continue to row 1000. Any help would be appreciated

--

Dave Peterson
.

Sorry about the typo.

Gald <vbg you found it.

JeffK wrote:

Never mind Dave I discovered the solution (a10:a1000)

Thanks for the help, you're always a great resource.

"Dave Peterson" wrote:

Try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .cells.Count 1 Then
Exit Sub
end if

If Intersect(me.Range("a10:1000"), .Cells) Is Nothing Then
'do nothing
else
Application.EnableEvents = False
With .offset(0,1)
.NumberFormat = "dd mmm yyyy"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub


I made some completely arbitrary changes (a block if/then instead of the single
line if/then). And I changed the "if .. is nothing" to something I find easier
to understand <vbg.

If you want, you can change back. It shouldn't be too difficult.


JeffK wrote:

I've used this Macro and need to tweak it:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("a10"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("b10")
.NumberFormat = "dd mmm yyyy"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub

This macro stamps B10 if something is entered in A10 but I also want this to
continue to row 1000. Any help would be appreciated

--

Dave Peterson
.


--

Dave Peterson