find next finds too much.

G

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
 
G

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top