Code Bug

J

Jack Foster

Hi...

I'm trying to time stamp a key stroke...when a user keys a
Y I want a cell 3 to the left top enter now(). I think the
code is perfect but it just won't work...Can anyone do
this better...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("AD8:$AD700")) Is Nothing
Then
If Not IsEmpty(Target) Then
If UCase(Target.Value) = "Y" Then
Target.Offset(0, 3).Value = Now()
Target.Offset(0, 3).NumberFormat = "dd/mm"
Target.EntireColumn.AutoFit
End If
End If
End If

Cheers

GC
 
B

Bob Phillips

Jack,

What is going wrong? Is it going 3 right instead of 3 up? If so, change
Offset(0, 3) to Offset(-3,0). If not, enlighten us.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

btadams

It seems to work for me. One thing you may want to do when you'r
changing a cells value using the Worksheet_Change event is to tur
events off before changing the cell and turn it back on afterwards:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A8:$A700")) Is Nothing Then
If Not IsEmpty(Target) Then
If UCase(Target.Value) = "Y" Then
Application.EnableEvents = False
Target.Offset(0, 3).Value = Now()
Target.Offset(0, 3).NumberFormat = "dd/mm"
Target.EntireColumn.AutoFit
Application.EnableEvents = True
End If
End If
End If
End Su
 
Top