Hi all I have some code below that looks for a date in an array o
sheets when it finds it select an offset and colour it red this work
(sort of!) but after it has found the cell im looking for and coloure
it it then does the same for the next cell below the one t found and s
on......how can i smarten this up and get it only to act on the criteri
i set?

Hope you can help!
Regards,
Simon
P.S i have included the rest of the code that is used within th
userform, staffdates is in the userform module!

Sub staffdates()

Dim wks As Worksheet
Dim rng As Range
Dim arr As Variant
Dim mycell
dv = ComboBox2.Text
sn = ComboBox1.Text

arr = Array("Week1", "Week2", "Week3", "Week4", "Week5", _
"Week6")

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
MsgBox "found " & mycell.Text
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

Next mycell
Exit Sub
Worksheets("Week Selection").Visible = True
wks.Visible = xlSheetHidden
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 Su

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=55906