Maybe...

Option Explicit
Sub staffdates()

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

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

Application.EnableEvents = False

For Each wks In Worksheets(arr)
FoundIt = False
wks.Visible = xlSheetVisible
wks.Select 'so you can select the ranges below
Set rng = wks.Range("A1:A300")
For Each mycell In rng

'what did this do?
'If mycell.Text = dv Then
'End If

'more testing stuff
'MsgBox "found " & mycell.Text
'Sheets("Week Selection").Visible = False

With wks
If sn = "Lauren" Then
mycell.Offset(1, 1).Select
FoundIt = True
ElseIf sn = "Emma" Then
mycell.Offset(1, 5).Select
FoundIt = True
ElseIf sn = "Cheryl" Then
mycell.Offset(1, 9).Select
FoundIt = True
End If
End With

If FoundIt = True Then
Call cchange
Exit For 'leave that worksheet
End If
Next mycell

Worksheets("Week Selection").Visible = True
wks.Visible = xlSheetHidden
Next wks

Application.EnableEvents = True

Unload Me
End Sub

==========
Or maybe without the selecting...

Option Explicit
Sub staffdates()

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

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

Application.EnableEvents = False

For Each wks In Worksheets(arr)
FoundIt = False
wks.Visible = xlSheetVisible
wks.Select 'so you can select the ranges below
Set rng = wks.Range("A1:A300")
For Each mycell In rng.Cells
With wks
If sn = "Lauren" Then
Call cchange(mycell.Offset(1, 1))
FoundIt = True
ElseIf sn = "Emma" Then
Call cchange(mycell.Offset(1, 5))
FoundIt = True
ElseIf sn = "Cheryl" Then
Call cchange(mycell.Offset(1, 9))
FoundIt = True
End If
End With

If FoundIt = True Then
'Call cchange
Exit For 'leave that worksheet
End If
Next mycell
Next wks

Application.EnableEvents = True

Unload Me
End Sub

Sub cchange(myRng As Range)
With myRng.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
'Unload UserForm3

End Sub

All untested!

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

--

Dave Peterson