I was attempting to edit the original post, but my time expired:
I've added more details here
This code is used in a Scheduling workbook to alert the user that there is a scheduling conflict...and it works well, HOWEVER:
This only represents 1 DEPARTMENT for 1 full Week. - Servers, Wed-Tue, AM Shift & PM Shift
In reality, there are 7 departments (Servers, Bussers, Drivers, Pizza Makers, Fry Cooks, Call Center, & Maintenance). All departments have both AM and PM shifts 7 days a week. The schedulers are able to work on 2 weeks worth of schedules.
So the code below would need to be repeated 11 more times with slight variations that would address the different ranges.
Server Schedule A (7 days, AM & PM shifts)
Server Schedule B (7 days, AM & PM shifts)
Busser Schedule A (7 days, AM & PM shifts)
Busser Schedule B (7 days, AM & PM shifts) etc.
NOTE: To handle double scheduling, the workbook relies on several conditional formatting rules that do not work across different sheets. Therefore, 14 days of schedules are located on one sheet.
This is a screen shot of the complete schedule template (zoomed out). The top 14 are a shared schedule for 4 smaller departments, the bottom are shared by 2 larger departments.
Shared with Dropbox
www.dropbox.com
This is a screenshot of the 5 dept. daily schedule close up:
Shared with Dropbox
www.dropbox.com
This is a screenshot of the 2 dept. daily schedule close up:
Shared with Dropbox
www.dropbox.com
This is a screenshot of the Server Availability Data. This is where the "Sheet2" references are drawn from. The data validation for the Servers is B11:B800
(Each department has a sheet like this):
Shared with Dropbox
www.dropbox.com
Should I make 7 different subs (One for each Department?)
Should it be 196 seperate subs (One for each shift [7dept*7days*2shifts*2schedules])?
Should I continue to nest them in the same sub?
Or is there a more efficient way to write this? (this is my assumtion)
VBA Code:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim bFound As Boolean
Dim rCell As Range
bFound = False
'WEDNESDAY AM SERVER (Schedule A)
If Not Intersect(Target, Range("WED_AM_SER")) Is Nothing Then
For Each rCell In Sheet2.Range("S11:S800") 'Me refers to the worksheet that the event macro is in.
If Trim(rCell.Value) = Trim(Target.Value) _
Then
bFound = True
Exit For
End If
Next rCell
If Not bFound _
Then
MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
End If
End If
'WEDNESDAY PM SERVER (Schedule A)
If Not Intersect(Target, Range("WED_PM_SER")) Is Nothing Then
For Each rCell In Sheet2.Range("U11:U800") 'Me refers to the worksheet that the event macro is in.
If Trim(rCell.Value) = Trim(Target.Value) _
Then
bFound = True
Exit For
End If
Next rCell
If Not bFound _
Then
MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
End If
End If
'THURSDAY AM SERVER (Schedule A)
If Not Intersect(Target, Range("THU_AM_SER")) Is Nothing Then
For Each rCell In Sheet2.Range("Z11:Z800") 'Me refers to the worksheet that the event macro is in.
If Trim(rCell.Value) = Trim(Target.Value) _
Then
bFound = True
Exit For
End If
Next rCell
If Not bFound _
Then
MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
End If
End If
'THURSDAY PM SERVER (Schedule A)
If Not Intersect(Target, Range("THU_PM_SER")) Is Nothing Then
For Each rCell In Sheet2.Range("AB11:AB800") 'Me refers to the worksheet that the event macro is in.
If Trim(rCell.Value) = Trim(Target.Value) _
Then
bFound = True
Exit For
End If
Next rCell
If Not bFound _
Then
MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
End If
End If
'FRIDAY AM SERVER (Schedule A)
If Not Intersect(Target, Range("FRI_AM_SER")) Is Nothing Then
For Each rCell In Sheet2.Range("AG11:AG800") 'Me refers to the worksheet that the event macro is in.
If Trim(rCell.Value) = Trim(Target.Value) _
Then
bFound = True
Exit For
End If
Next rCell
If Not bFound _
Then
MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
End If
End If
'FRIDAY PM SERVER (Schedule A)
If Not Intersect(Target, Range("FRI_PM_SER")) Is Nothing Then
For Each rCell In Sheet2.Range("AI11:AI800") 'Me refers to the worksheet that the event macro is in.
If Trim(rCell.Value) = Trim(Target.Value) _
Then
bFound = True
Exit For
End If
Next rCell
If Not bFound _
Then
MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
End If
End If
'SATURDAY AM SERVER (Schedule A)
If Not Intersect(Target, Range("SAT_AM_SER")) Is Nothing Then
For Each rCell In Sheet2.Range("AN11:AN800") 'Me refers to the worksheet that the event macro is in.
If Trim(rCell.Value) = Trim(Target.Value) _
Then
bFound = True
Exit For
End If
Next rCell
If Not bFound _
Then
MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
End If
End If
'SATURDAY PM SERVER (Schedule A)
If Not Intersect(Target, Range("SAT_PM_SER")) Is Nothing Then
For Each rCell In Sheet2.Range("AP11:AP800") 'Me refers to the worksheet that the event macro is in.
If Trim(rCell.Value) = Trim(Target.Value) _
Then
bFound = True
Exit For
End If
Next rCell
If Not bFound _
Then
MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
End If
End If
'SUNDAY AM SERVER (Schedule A)
If Not Intersect(Target, Range("SUN_AM_SER")) Is Nothing Then
For Each rCell In Sheet2.Range("AU11:AU800") 'Me refers to the worksheet that the event macro is in.
If Trim(rCell.Value) = Trim(Target.Value) _
Then
bFound = True
Exit For
End If
Next rCell
If Not bFound _
Then
MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
End If
End If
'SUNDAY PM SERVER (Schedule A)
If Not Intersect(Target, Range("SUN_PM_SER")) Is Nothing Then
For Each rCell In Sheet2.Range("AW11:AW800") 'Me refers to the worksheet that the event macro is in.
If Trim(rCell.Value) = Trim(Target.Value) _
Then
bFound = True
Exit For
End If
Next rCell
If Not bFound _
Then
MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
End If
End If
'MONDAY AM SERVER (Schedule A)
If Not Intersect(Target, Range("MON_AM_SER")) Is Nothing Then
For Each rCell In Sheet2.Range("E11:E800") 'Me refers to the worksheet that the event macro is in.
If Trim(rCell.Value) = Trim(Target.Value) _
Then
bFound = True
Exit For
End If
Next rCell
If Not bFound _
Then
MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
End If
End If
'MONDAY PM SERVER (Schedule A)
If Not Intersect(Target, Range("MON_PM_SER")) Is Nothing Then
For Each rCell In Sheet2.Range("G11:G800") 'Me refers to the worksheet that the event macro is in.
If Trim(rCell.Value) = Trim(Target.Value) _
Then
bFound = True
Exit For
End If
Next rCell
If Not bFound _
Then
MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
End If
End If
'TUESDAY AM SERVER (Schedule A)
If Not Intersect(Target, Range("TUE_AM_SER")) Is Nothing Then
For Each rCell In Sheet2.Range("L11:L800") 'Me refers to the worksheet that the event macro is in.
If Trim(rCell.Value) = Trim(Target.Value) _
Then
bFound = True
Exit For
End If
Next rCell
If Not bFound _
Then
MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
End If
End If
'TUESDAY PM SERVER (Schedule A)
If Not Intersect(Target, Range("TUE_PM_SER")) Is Nothing Then
For Each rCell In Sheet2.Range("N11:N800") 'Me refers to the worksheet that the event macro is in.
If Trim(rCell.Value) = Trim(Target.Value) _
Then
bFound = True
Exit For
End If
Next rCell
If Not bFound _
Then
MsgBox "There is an AVAILABILITY CONFLICT with this Employee", vbCritical
End If
End If
End Sub
Original thread and full explanation
CAN BE FOUND HERE