On 12/08/2010 10:18, Andy wrote:
I still haven't managed to find a solution to this - any help would be
appreciated.

As the above code is basically repeated for 23 other cells I'm not
sure if it can function properly when a block of cells are pasted in,
although I have tested by pasting a single row as well.

If necessary I can try to upload the template.

Andy,

Apologies if I have missed the point of your question.

The test code below detects if date entered manually or pasted into a
cell in a range beginning C11 and ending at a variable cell below it in
Column C contains a time between 11:00 and 11:59.

The test code gives some messages to show how it is working.

Cells with invalid times are blanked.

You can enter a single time manually or paste a range into column C.

I hope this is of some help.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

Dim C22ValRange As Range
Dim DataOK As Boolean
Dim msg As String
Dim rCell As Range
Dim sCell As Range
Dim endCell As Range

Set endCell = Cells(Rows.Count, "C").End(xlUp)

Set C22ValRange = Range("C11" & ":" & endCell.Address)
'MsgBox endCell.Address
'MsgBox C22ValRange.Address


For Each rCell In C22ValRange
For Each sCell In Target
If rCell.Address = sCell.Address Then
MsgBox "Cell " & rCell.Address
If rCell.Value = #11:00:00 AM# And rCell.Value <= #11:59:00
AM# Then
MsgBox "Valid Time " & rCell.Address
Else
MsgBox "Invalid Time " & rCell.Address
rCell = ""
End If
End If
Next sCell
Next rCell
Application.EnableEvents = True
End Sub