Gentlemen....thanks for the response, Jim your solution did indeed find
the offset an colour it, the rogue End If was for the first criteria to
be looked for and that was the value of Combobox2 and then perform the
offset...but i kind of messed that bit up....Dave, your solutions
worked as well finding the first offset and colouring them....but did
it on all pages! again probably because of my omission for the first
criteria.

I had a brainwave (well more of a ripple!) rather than going to the
trouble of colouring the required cell and then having to colour it
back manually is it possible with the code below to look at a named
range called StaffHols find a match for combobox2 (which would appear
in the first column of the named range) then when the code looks at the
specific sheet and then day look at the first cell in the selected day
range (in the select case below) and if the date in that cell matches
any of the dates (in column 2 of the named range) opposite the match
for the name found in the named range then MsgBox blah blah and back to
the userform so they can choose another person or sheet or day.

More of a brain dump than wave but i will try to clarify further if you
need!
Regards,
Simon

Public Sub FindSlot()

Dim rng As Range
Dim w, t, s As Variant
Dim r As Range
Dim mycell

Application.EnableEvents = False
w = UserForm2.ComboBox3.Value ''''Contains the name of the worksheet to
look in
s = UserForm2.ComboBox2.Value ''''Contains the name of the person to
look at
Worksheets(w).Visible = True
Worksheets(w).Select
t = UserForm2.ComboBox1.Value 'Contains which day to look at

With Worksheets(w)
Select Case t
Case Is = "Tuesday"
Set r = .Range("A4:A46")
Case Is = "Wednesday"
Set r = .Range("A49:A94")
Case Is = "Thursday"
Set r = .Range("A97:A142")
Case Is = "Friday"
Set r = .Range("A145:A190")
Case Is = "Saturday"
Set r = .Range("A193:A238")
End Select
End With

On Error GoTo cls
Application.EnableEvents = False

For Each mycell In r
If mycell.Text = UserForm2.ListBox1.Text Then ''''Listbox1 contains a
time to look at
mycell.Select
UserForm2.Hide
Select Case s
Case Is = "Lauren"
c = 1: GoSub TestSlot
Case Is = "Emma"
c = 5: GoSub TestSlot
Case Is = "Cheryl"
c = 9: GoSub TestSlot
End Select

End If
Next mycell

Worksheets("Week Selection").Visible = True
Worksheets(w).Visible = False

cls:
Application.EnableEvents = True
Unload UserForm2

Exit Sub

TestSlot:
If mycell.Offset(0, c) < "" And mycell.Offset(0, c + 2) < "" Then
Msg = "Please Choose New Time, Day or Week... " & mycell.Value & "
For " & s & " Is Taken!"
MsgBox Msg, vbOKOnly, "Time Slot Taken"
UserForm2.Show
ElseIf mycell.Offset(0, c) = "" Or mycell.Offset(0, c + 2) = ""
Then
Answer = MsgBox(" Chosen Time Has An Empty Slot" & Chr(13) &
"Click Yes to Make Booking or Click No To Exit", vbYesNo, "Make A
Booking?")
If Answer = vbYes Then
Unload UserForm2
UserForm1.Show
End If
End If
Return

End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=559067