You probably want to use an exit for statement and a boolean flag to indicate
whether you want to keep going or not...

Sub staffdates()

Dim wks As Worksheet
Dim rng As Range
Dim arr As Variant
Dim mycell
Dim blnFound as Boolean

dv = ComboBox2.Text
sn = ComboBox1.Text

arr = Array("Week1", "Week2", "Week3", "Week4", "Week5", _
"Week6")
blnFound = false
Application.EnableEvents = False

For Each wks In Worksheets(arr)
wks.Visible = xlSheetVisible
Set rng = Sheets(wks.Name).Range("A1:A300")
For Each mycell In rng

If mycell.Text = dv Then
End If '**What is this for???
MsgBox "found " & mycell.Text
blnFound = true
Sheets("Week Selection").Visible = False
With Worksheets(arr)
If sn = "Lauren" Then
mycell.Offset(1, 1).Select
ElseIf sn = "Emma" Then
mycell.Offset(1, 5).Select
ElseIf sn = "Cheryl" Then
mycell.Offset(1, 9).Select
End If
End With
Call cchange
if blnfound then exit for
Next mycell
Exit Sub
Worksheets("Week Selection").Visible = True
wks.Visible = xlSheetHidden
if blnfound then exit for
Next wks

Application.EnableEvents = True

Unload Me
End Sub

Sub cchange()
With Selection.Interior
..ColorIndex = 3
..Pattern = xlSolid
..PatternColorIndex = xlAutomatic
End With
Unload UserForm3
Exit Sub
End Sub

Private Sub ComboBox2_Change()
ComboBox2 = Format(ComboBox2.Value, "dd mmmm yyyy")
End Sub

Private Sub CommandButton1_Click()
Call staffdates

End Sub

--
HTH...

Jim Thomlinson


"Simon Lloyd" wrote:


Hi Jim thanks for replying, once it has found the date selected on the
userform and then performed the offset depending on the criteria
selected in the remaining combobox on the userform it should change the
colour of the selected offset and then end as there will be no
duplicates of the date on any of the sheets!, trouble is it isnt ending
it then selects another offset below the last one and carries on, the
only way it doesnt colour a whole row is because i have put a MsgBox in
to show me that it has found what i am looking for!

Regards,
Simon


--
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