find next finds too much.

Greeting,
I am developing something for a user.
i have 2 cascading combo that corrosponds with data on the
sheet. the user wants the combo to change the color on the
data on the sheet when she selects something from the
combo box.
I have achieved this. however... the find next part of the
code goes through the range what seems like 50 times be
for it stops causing a delay of about 3-5 seconds.
meanwhile the now colored cells just flicker as the code
loops through the range over and over. I have tried
several thing but nothing works. i'm stumped. How do i
have the code go through the range only once.
heres the code i have so far....
it is the for next loop that is doing it.
Private Sub ComboBox2_Change()
Dim c As String
Dim rng As Range
Dim cell As Range
c = Me.ComboBox2.Value
Set rng = Range("A1:R33")
rng.Interior.ColorIndex = xlNone
If c = "" Then
rng.Interior.ColorIndex = xlNone
Else
Cells.Find(What:=c, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Interior.ColorIndex = 42
If ActiveCell.Interior.ColorIndex = 42 Then
For Each cell In rng
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Interior.ColorIndex = 42
Next
End If
End If
End Sub

Is that for next loop simply supposed to color the range of A1:R33?

no. It is looking of what is in the combo box and if it
finds a match, it colors the interia on that cell green.

>-----Original Message-----
>Is that for next loop simply supposed to color the range
of A1:R33?
>
>.
>

Private Sub ComboBox2_Change()
Dim c As String
Dim rng As Range
Dim cell As Range
Dim sAddr as String
c = Me.ComboBox2.Value
Range("A1:R33").Interior.ColorIndex = xlNone
If c = "" Then Exit Sub
set rng = Range("A1:R33").Find(What:=c, _
After:=Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
sAddr = rng.Address
Do
rng.Interior.ColorIndex = 42
set rng =Range("A1:R33").FindNext(rng)
loop until rng.Addr = sAddr
End if
End Sub

--
Regards,
Tom Ogilvy

<[email protected]> wrote in message
news:[email protected]...
> Greeting,
> I am developing something for a user.
> i have 2 cascading combo that corrosponds with data on the
> sheet. the user wants the combo to change the color on the
> data on the sheet when she selects something from the
> combo box.
> I have achieved this. however... the find next part of the
> code goes through the range what seems like 50 times be
> for it stops causing a delay of about 3-5 seconds.
> meanwhile the now colored cells just flicker as the code
> loops through the range over and over. I have tried
> several thing but nothing works. i'm stumped. How do i
> have the code go through the range only once.
> heres the code i have so far....
> it is the for next loop that is doing it.
> Private Sub ComboBox2_Change()
> Dim c As String
> Dim rng As Range
> Dim cell As Range
> c = Me.ComboBox2.Value
> Set rng = Range("A1:R33")
> rng.Interior.ColorIndex = xlNone
> If c = "" Then
> rng.Interior.ColorIndex = xlNone
> Else
> Cells.Find(What:=c, _
> After:=Range("A1"), _
> LookIn:=xlFormulas, _
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False).Activate
> ActiveCell.Interior.ColorIndex = 42
> If ActiveCell.Interior.ColorIndex = 42 Then
> For Each cell In rng
> Cells.FindNext(After:=ActiveCell).Activate
> ActiveCell.Interior.ColorIndex = 42
> Next
> End If
> End If
> End Sub
>

thanks tom. your rewrite worked perfectly.

>-----Original Message-----
>Private Sub ComboBox2_Change()
>Dim c As String
>Dim rng As Range
>Dim cell As Range
>Dim sAddr as String
>c = Me.ComboBox2.Value
>Range("A1:R33").Interior.ColorIndex = xlNone
>If c = "" Then Exit Sub
>set rng = Range("A1:R33").Find(What:=c, _
> After:=Range("A1"), _
> LookIn:=xlValues, _
> LookAt:=xlWhole, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
>if not rng is nothing then
> sAddr = rng.Address
> Do
> rng.Interior.ColorIndex = 42
> set rng =Range("A1:R33").FindNext(rng)
> loop until rng.Addr = sAddr
>End if
>End Sub
>
>--
>Regards,
>Tom Ogilvy
>
><[email protected]> wrote in message
>news:[email protected]...
>> Greeting,
>> I am developing something for a user.
>> i have 2 cascading combo that corrosponds with data on
the
>> sheet. the user wants the combo to change the color on
the
>> data on the sheet when she selects something from the
>> combo box.
>> I have achieved this. however... the find next part of
the
>> code goes through the range what seems like 50 times be
>> for it stops causing a delay of about 3-5 seconds.
>> meanwhile the now colored cells just flicker as the code
>> loops through the range over and over. I have tried
>> several thing but nothing works. i'm stumped. How do i
>> have the code go through the range only once.
>> heres the code i have so far....
>> it is the for next loop that is doing it.
>> Private Sub ComboBox2_Change()
>> Dim c As String
>> Dim rng As Range
>> Dim cell As Range
>> c = Me.ComboBox2.Value
>> Set rng = Range("A1:R33")
>> rng.Interior.ColorIndex = xlNone
>> If c = "" Then
>> rng.Interior.ColorIndex = xlNone
>> Else
>> Cells.Find(What:=c, _
>> After:=Range("A1"), _
>> LookIn:=xlFormulas, _
>> LookAt:=xlPart, _
>> SearchOrder:=xlByRows, _
>> SearchDirection:=xlNext, _
>> MatchCase:=False).Activate
>> ActiveCell.Interior.ColorIndex = 42
>> If ActiveCell.Interior.ColorIndex = 42 Then
>> For Each cell In rng
>> Cells.FindNext
(After:=ActiveCell).Activate
>> ActiveCell.Interior.ColorIndex = 42
>> Next
>> End If
>> End If
>> End Sub
>>
>
>
>.
>

There was a typo near the bottom

Private Sub ComboBox2_Change()
Dim c As String
Dim rng As Range
Dim cell As Range
Dim sAddr as String
c = Me.ComboBox2.Value
Range("A1:R33").Interior.ColorIndex = xlNone
If c = "" Then Exit Sub
set rng = Range("A1:R33").Find(What:=c, _
After:=Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
sAddr = rng.Address
Do
rng.Interior.ColorIndex = 42
set rng =Range("A1:R33").FindNext(rng)
loop until rng.Address = sAddr '<====
End if
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> Private Sub ComboBox2_Change()
> Dim c As String
> Dim rng As Range
> Dim cell As Range
> Dim sAddr as String
> c = Me.ComboBox2.Value
> Range("A1:R33").Interior.ColorIndex = xlNone
> If c = "" Then Exit Sub
> set rng = Range("A1:R33").Find(What:=c, _
> After:=Range("A1"), _
> LookIn:=xlValues, _
> LookAt:=xlWhole, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
> if not rng is nothing then
> sAddr = rng.Address
> Do
> rng.Interior.ColorIndex = 42
> set rng =Range("A1:R33").FindNext(rng)
> loop until rng.Addr = sAddr
> End if
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Greeting,
> > I am developing something for a user.
> > i have 2 cascading combo that corrosponds with data on the
> > sheet. the user wants the combo to change the color on the
> > data on the sheet when she selects something from the
> > combo box.
> > I have achieved this. however... the find next part of the
> > code goes through the range what seems like 50 times be
> > for it stops causing a delay of about 3-5 seconds.
> > meanwhile the now colored cells just flicker as the code
> > loops through the range over and over. I have tried
> > several thing but nothing works. i'm stumped. How do i
> > have the code go through the range only once.
> > heres the code i have so far....
> > it is the for next loop that is doing it.
> > Private Sub ComboBox2_Change()
> > Dim c As String
> > Dim rng As Range
> > Dim cell As Range
> > c = Me.ComboBox2.Value
> > Set rng = Range("A1:R33")
> > rng.Interior.ColorIndex = xlNone
> > If c = "" Then
> > rng.Interior.ColorIndex = xlNone
> > Else
> > Cells.Find(What:=c, _
> > After:=Range("A1"), _
> > LookIn:=xlFormulas, _
> > LookAt:=xlPart, _
> > SearchOrder:=xlByRows, _
> > SearchDirection:=xlNext, _
> > MatchCase:=False).Activate
> > ActiveCell.Interior.ColorIndex = 42
> > If ActiveCell.Interior.ColorIndex = 42 Then
> > For Each cell In rng
> > Cells.FindNext(After:=ActiveCell).Activate
> > ActiveCell.Interior.ColorIndex = 42
> > Next
> > End If
> > End If
> > End Sub
> >
>
>