Hi Martin,


Trying the code out it works well if the cell is directly entered. But if
you copy down or you paste into more than one cell it does not enter
the date.

Is there any solution to this problem?

The code was specifically limited to a single cell by the line:

If .Count 1 Then Exit Sub


Is there any solution to this problem?

Try something like::

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

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

Application.EnableEvents = False
For Each rCell In rng
With rCell
If Not Intersect(rng, .Cells) Is Nothing Then
If Not IsEmpty(.Value) Then
With .Offset(0, 1)
If IsEmpty(.Value) Then
.Value = FormatDateTime(Now, vbLongTime)
End If
End With
End If
End If
End With
Next rCell
Application.EnableEvents = True
End Sub
'<<=============


---
Regards,
Norman


"Martin Fishlock" wrote in
message ...
Norman,

Thanks for the answer to systematic's question.

Trying the code out it works well if the cell is directly entered. But if
you copy down or you paste into more than one cell it does not enter the
date.

Is there any solution to this problem?

TIA.



"Norman Jones" wrote:

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