date and time entered when a cell contains data

i am trying to use vba code to enter the date and time when a certain field
contains data.the code i have tried to use is:
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Target.Address = "$B$10":"$B$40" Then
Range("A10:A40") = Time
Range("G10:G40") = Date
End If
Application.EnableEvents = True

End Sub
please could you help
thank you John Tempest

John,
What would you like to do? Let something happen after someone changed a cel
in the range b10 : b40 ?
grtx's, Martijn

"john tempest" <[email protected]> schreef in bericht
news:[email protected]...
> i am trying to use vba code to enter the date and time when a certain
field
> contains data.the code i have tried to use is:
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Application.EnableEvents = False
> If Target.Address = "$B$10":"$B$40" Then
> Range("A10:A40") = Time
> Range("G10:G40") = Date
> End If
> Application.EnableEvents = True
>
> End Sub
> please could you help
> thank you John Tempest

john tempest:

try it

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Set bb = Union(Range("B10:B40"), Target)
If Union(Target, Range("B10:B40")).Address = Range("B10:B40").Address Then
Range("A10:A40") = Time
Range("G10:G40") = Date
End If
Application.EnableEvents = True
End Sub

or


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Set bb = Union(Range("B10:B40"), Target)
If Union(Target, Range("B10:B40")).Address = Range("B10:B40").Address Then
Cells(Target.Row, "A") = Time
Cells(Target.Row, "G") = Date
End If
Application.EnableEvents = True
End Sub


--
天行健，君�*�以自強不息
地勢坤，君�*�以厚德載物

http://www.vba.com.tw/plog/


"john tempest" wrote:

> i am trying to use vba code to enter the date and time when a certain field
> contains data.the code i have tried to use is:
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Application.EnableEvents = False
> If Target.Address = "$B$10":"$B$40" Then
> Range("A10:A40") = Time
> Range("G10:G40") = Date
> End If
> Application.EnableEvents = True
>
> End Sub
> please could you help
> thank you John Tempest

chijanzen
thankyou, your second piece of code is just what i was looking for
regards john tempest

"chijanzen" wrote:

> john tempest:
>
> try it
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.EnableEvents = False
> Set bb = Union(Range("B10:B40"), Target)
> If Union(Target, Range("B10:B40")).Address = Range("B10:B40").Address Then
> Range("A10:A40") = Time
> Range("G10:G40") = Date
> End If
> Application.EnableEvents = True
> End Sub
>
> or
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.EnableEvents = False
> Set bb = Union(Range("B10:B40"), Target)
> If Union(Target, Range("B10:B40")).Address = Range("B10:B40").Address Then
> Cells(Target.Row, "A") = Time
> Cells(Target.Row, "G") = Date
> End If
> Application.EnableEvents = True
> End Sub
>
>
> --
> 天行健，君�*�以自強不息
> 地勢坤，君�*�以厚德載物
>
> http://www.vba.com.tw/plog/
>
>
> "john tempest" wrote:
>
> > i am trying to use vba code to enter the date and time when a certain field
> > contains data.the code i have tried to use is:
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> > Application.EnableEvents = False
> > If Target.Address = "$B$10":"$B$40" Then
> > Range("A10:A40") = Time
> > Range("G10:G40") = Date
> > End If
> > Application.EnableEvents = True
> >
> > End Sub
> > please could you help
> > thank you John Tempest