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