John,
I mean that I am reading that the cell will contain say '0800 - 15:00', not
a time value, so you test for a string not a number. If you have 8 it is
probably best to use worksheet events. Here is an example.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C1:C50")) Is Nothing Then
With Target
Select Case UCase(.Value)
Case "00:01 - 03:00": .Interior.ColorIndex = 34
Case "03:01 - 08:00": .Interior.ColorIndex = 35
Case "08:01 - 15:00": .Interior.ColorIndex = 5
Case "15:01 - 18:00": .Interior.ColorIndex = 46
Case "18:01 - 21:00": .Interior.ColorIndex = 7
Case "21:01 - 24:00": .Interior.ColorIndex = 3
'etc.
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
--
HTH
RP
(remove nothere from the email address if mailing direct)
