Worksheet_Change problem

[liampog]

Hi there

At the moment I have a Worksheet_Change event set up to monitor a range of cells.

The range of cells in question have a Data Validation list setup for a dropdown selection of values which are a number of "Shift Times", for example:

13:30 - 21:30
14:30 - 22:30
19:00 - 03:00
21:00 - 05:00
22:30 - 06:30

At the moment, the Worksheet_Change is set up to perform a macro when the exact text of each of these values is entered.

Is there any way that I can have it perform macros depending on two conditions within one cell. For example, if the cell is changed to start with the first five characters being 19:00 it runs a macro and with the last five characters being 03:00 it runs a different macro.

At the moment, if anybody enters a Shift Time that hasn't been pre-programmed for a macro to run when the EXACT shift time has been entered into the cell, it won't do the required task.

Basically, I want the monitored cells to be Universal in that ANY shift time can be entered two macros are run depending on the start and finish time of the shift.

Would this be utilised with the LEFT(A1,5) and RIGHT (A1,5) commands? Or can these not be used in the Worksheet_Change part.

The code that I use at the moment is as follows:

    Private Sub Worksheet_Change(ByVal Target As Range)
    
       If Not Intersect(Target, Range("C10:C46")) Is Nothing Then

          Select Case Target.Value
        
               Case Is = "12:00 - 20:00":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "13:30 - 21:30":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "14:30 - 22:30":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "16:00 - 02:00":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "18:00 - 02:00":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "19:00 - 03:00":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "19:00 - 05:00":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "19:00 - 07:00":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "20:00 - 04:00":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "20:00 - 06:00":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "20:30 - 06:30":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "21:00 - 05:00":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "21:00 - 06:00":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "21:00 - 07:00":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "21:30 - 06:30":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "22:00 - 06:00":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "22:30 - 06:30":

                  CODE FOR THIS SPECIFIC SHIFT TIME
        
               Case Is = "":

                  CODE FOR A SHIFT TIME BEING DELETED
        
          End Select
        
       End If

    End Sub

 

[Tom Urtis]

Maybe this approach might get you on the right track.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C10:C46")) Is Nothing Then
With Target
 
'Start times
Select Case Left(.Value, 5)
 
Case "12:00"
'Run your macro for the 12:00 start time
 
Case "13:30"
'Run your macro for the 13:30 start time
 
'and so on and so forth for start times 
End Select 'for start times
 
 
'=========================================
 
 
'End times
Select Case Right(.Value, 5)
 
Case "21:30"
'Run your macro for the 21:30 end time
 
Case "03:00"
'Run your macro for the 03:00 end time
 
'and so on and so forth for end times
End Select 'for end times
 
 
End With
End If
End Sub

 

[liampog]

Brilliant, this works a treat!

Being able to do this means I never have to go in and add programming so that new shift times can be created!

Liam