Hi Rob,

Try something like:

'=============
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range

Set rng = Me.Range("F6:F45")

With Target
If .Count 1 Then Exit Sub
If Not Intersect(rng, .Cells) Is Nothing Then
Application.EnableEvents = False
If Not IsEmpty(.Value) Then
.Offset(0, 1).Value = FormatDateTime(Now, vbLongTime)
End If
Application.EnableEvents = True
End If
End With
End Sub
'<<=============


---
Regards,
Norman


"systematic" wrote
in message ...

Hi there,

I'm trying to write a macro, so that if a cell in a range has a value
placed in it - Now() is written to a corresponding cell in another
column, then the value copied and pasted (so that it does not update).

I have this -

private sub worksheet_change(byval target as range)

if target.address(false, false) = \"f6:f45\" then
application.enableevents = false

if range(\"f6:f45\") = \"1\" then
range(\"d6:d45\").value = now()

else
range(\"d6:d45\") = \"no call taken\"

end if

application.enableevents = true

end if

range(\"d6:d45\").select
selection.copy
range(\"d6:d45\").select
selection.pastespecial paste:=xlpastevalues, operation:=xlnone,
skipblanks _
:=false, transpose:=false

end sub

For some reason it does not want to work for me. I have EnableEvents
set to True on workbook open and am running Office XP.

Would appreciate any advice to steer me in the right direction as I am
still quite new to VBA!

Thanks

Rob


--
systematic
------------------------------------------------------------------------
systematic's Profile:
http://www.excelforum.com/member.php...o&userid=25294
View this thread: http://www.excelforum.com/showthread...hreadid=496107