For next loop carries on after criteria is met changing other cells????

I'm lost. What exactly is ist supposed to do. Specifially when is it supposed
to end.
--
HTH...

Jim Thomlinson


"Simon Lloyd" wrote:

>
> Hi all I have some code below that looks for a date in an array of
> sheets when it finds it select an offset and colour it red this works
> (sort of!) but after it has found the cell im looking for and coloured
> it it then does the same for the next cell below the one t found and so
> on......how can i smarten this up and get it only to act on the criteria
> i set?
>
> Hope you can help!
> Regards,
> Simon
> P.S i have included the rest of the code that is used within the
> 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 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
>
>

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 Column is because i have put a MsgBox in to show me that it has found what i am looking for!

Regards,
Simon

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

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