On 12/08/2010 16:08, Andy wrote:
Thanks for the reply.

Ideally I would like to cut the code down but the problem is that each
cell in column C needs different validation. Such as C11 should be
between 00:00 and 00:59, C12 should be between 01:00 and 01:59 and so
on until C34 so the code so far is below.


Hi Andy,

For your range C11:C34 this code should identify which cells have a
valid time using your defined categories.

I haven't tried to mimic the action of your code, just what I think you
are trying to achieve with the time comparison and the ability to paste
into the range C11:C34 and still get the result correct comparison.

The code assumes the source paste range and the source range are
formatted as time.

It isn't particularly short as the time boundaries have to be defined
somewhere.

I'd thought of trying to use an enum but I haven't figured that out yet.

Just back from a 5 hr drive so my mind is not at its sharpest ;)


Option Explicit
Option Base 1
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
Dim cellRow As Long

Dim Times(24, 2) As Date


Times(1, 1) = #12:00:00 AM#
Times(1, 2) = #12:59:00 AM#
Times(2, 1) = #1:00:00 AM#
Times(2, 2) = #1:59:00 AM#
Times(3, 1) = #2:00:00 AM#
Times(3, 2) = #2:59:00 AM#
Times(4, 1) = #3:00:00 AM#
Times(4, 2) = #3:59:00 AM#
Times(5, 1) = #4:00:00 AM#
Times(5, 2) = #4:59:00 AM#
Times(6, 1) = #5:00:00 AM#
Times(6, 2) = #5:59:00 AM#
Times(7, 1) = #6:00:00 AM#
Times(7, 2) = #6:59:00 AM#
Times(8, 1) = #7:00:00 AM#
Times(8, 2) = #7:59:00 AM#
Times(9, 1) = #8:00:00 AM#
Times(9, 2) = #8:59:00 AM#
Times(10, 1) = #9:00:00 AM#
Times(10, 2) = #9:59:00 AM#
Times(11, 1) = #10:00:00 AM#
Times(11, 2) = #10:59:00 AM#
Times(12, 1) = #11:00:00 AM#
Times(12, 2) = #11:59:00 AM#
Times(13, 1) = #12:00:00 PM#
Times(13, 2) = #12:59:00 PM#
Times(14, 1) = #1:00:00 PM#
Times(14, 2) = #1:59:00 PM#
Times(15, 1) = #2:00:00 PM#
Times(15, 2) = #2:59:00 PM#
Times(16, 1) = #3:00:00 PM#
Times(16, 2) = #3:59:00 PM#
Times(17, 1) = #4:00:00 PM#
Times(17, 2) = #4:59:00 PM#
Times(18, 1) = #5:00:00 PM#
Times(18, 2) = #5:59:00 PM#
Times(19, 1) = #6:00:00 PM#
Times(19, 2) = #6:59:00 PM#
Times(20, 1) = #7:00:00 PM#
Times(20, 2) = #7:59:00 PM#
Times(21, 1) = #8:00:00 PM#
Times(21, 2) = #8:59:00 PM#
Times(22, 1) = #9:00:00 PM#
Times(22, 2) = #9:59:00 PM#
Times(23, 1) = #10:00:00 PM#
Times(23, 2) = #10:59:00 PM#
Times(24, 1) = #11:00:00 PM#
Times(24, 2) = #11:59:00 PM#

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

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

For Each rCell In C22ValRange
For Each sCell In Target
If rCell.Address = sCell.Address Then
MsgBox "Cell " & rCell.Address
cellRow = rCell.Row - 10
MsgBox cellRow
If rCell.Value = Times(cellRow, 1) And rCell.Value <=
Times(cellRow, 2) 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