Sure, you can loop through the entire range of interest each time and update
cells where a date has not been entered as required ( a value is in a cell
within the range.). You could prevent copying into the range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Me.Range("F6:F45")

If Not Intersect(Target, rng) Is Nothing Then
Application.CutCopyMode = False
End If
End Sub

You could make a formula dependent on the range and utilize the calculate
event to clean up the range

You could use the selection change to affect the ability to drag

I don't know if any combination would be foolproof, but you could certainly
come close.

Obviously the more defensive/usually unnecessary checking you do, the more
it will affect performance.

--
Regards,
Tom Ogilvy




"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