Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find next finds too much.
Is that for next loop simply supposed to color the range of A1:R33?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find next finds too much.
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? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find next finds too much.
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 wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
find next finds too much.
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 wrote in message ... 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
find next finds too much.
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" wrote in 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 wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup only finds #n/a's | Excel Discussion (Misc queries) | |||
Which @function finds the mean of a number? | Excel Worksheet Functions | |||
Only finds first Occurrence | Excel Programming | |||
finds and concatenate | Excel Programming | |||
Find finds nothing, and errors | Excel Programming |