find next finds too much.

[Guest]

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

 

[Chip]

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

 

[Guest]

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.

 

[Tom Ogilvy]

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

 

[Guest]

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




.

 

[Tom Ogilvy]

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