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