Search function in excel

[DanUK86]

Ive created an excel spreadsheet for work to search available fitters but ive created a search string with an application box but my problem is it only highlights one word in the sheet. If i had dog in A1 and dog in B1 it would only highlight dog in A1, any one advise me on what i can do to get it to highlight all the words its found. Unformtunatley i haven't got my code with me as its on the work pc. If needed i can get hold of it. Thanks for any help.

 

[SkipVought]

Hi,

You tell us what you code is doing, but never post it.

Short of knowing exactly what techniques you are using, in a loop, search the ENTIRE sheet for ALL occurrences and MARK each one in the way you want.

Look at the Find and FindNext methods.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[DanUK86]

This is the VBA code i have used




Sub Button1_Click()
SearchString = Application.InputBox("What are you looking for?")
Range("A5:F1000").Select
Range("F1000").Activate
On Error GoTo Woops
Selection.Find(What:=SearchString, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
On Error GoTo 0
Exit Sub
Woops:
Range("A1").Select
End Sub

 

[SkipVought]

Sub Button1_Click()
    Dim r As Range, SearchString As String, nPrev
    SearchString = Application.InputBox("What are you looking for?")
    On Error GoTo Woops
    nPrev = 0
    Set r = Range("A5:F1000").Find(What:=SearchString)
    Do
        If Not r Is Nothing Then
            With r
                .Interior.ColorIndex = 22
            End With
            nPrev = r.Row * 10 + r.Column
            Set r = Range("A5:F1000").Find(What:=SearchString, After:=r)
        Else
            Exit Do
        End If
    Loop While r.Row * 10 + r.Column > nPrev
    
    On Error GoTo 0
    Exit Sub
Woops:
    Range("A1").Select
End Sub

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[DanUK86]

Brilliant ill give it a go tomorrow. Thanks

 

[DanUK86]

Brilliant that worked a treat but one more problem sorry. When it finds the item your looking for it colours the cell in red (All sheets in an aqua colour) and they stay red unless you go in and change the colour of each cell manually. Is there a way around this so when the user saves the workbook it removes all the highlighted cells back to its normal colour?

 

[SkipVought]

Sub Button1_Click()
    Dim r As Range, SearchString As String, nPrev
    SearchString = Application.InputBox("What are you looking for?")
    On Error GoTo Woops
    nPrev = 0
[b]
    cells.interior.colorindex = xlnone 'or choose a colorindex - use this code to clear all also
[/b]
    Set r = Range("A5:F1000").Find(What:=SearchString)
    Do
        If Not r Is Nothing Then
            With r
                .Interior.ColorIndex = 22
            End With
            nPrev = r.Row * 10 + r.Column
            Set r = Range("A5:F1000").Find(What:=SearchString, After:=r)
        Else
            Exit Do
        End If
    Loop While r.Row * 10 + r.Column > nPrev
    
    On Error GoTo 0
    Exit Sub
Woops:
    Range("A1").Select
End Sub

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[DanUK86]

Sorry but where would i put that so once its found the data and highlighted it in the red it'll clear before it saves. This'll be the last question thanks for your quick replies. Ive been searching on the internet for weeks

 

[SkipVought]

Second thought, run ClearHighlights whenever you want to... clear the highlights!

[b]
sub ClearHighlights()
    cells.interior.colorindex = xlnone 'or choose a colorindex - use this code to clear all also

end sub[/b]
Sub Button1_Click()
    Dim r As Range, SearchString As String, nPrev
    SearchString = Application.InputBox("What are you looking for?")
    On Error GoTo Woops
    nPrev = 0
[b]
    ClearHighlights
[/b]
    Set r = Range("A5:F1000").Find(What:=SearchString)
    Do
        If Not r Is Nothing Then
            With r
                .Interior.ColorIndex = 22
            End With
            nPrev = r.Row * 10 + r.Column
            Set r = Range("A5:F1000").Find(What:=SearchString, After:=r)
        Else
            Exit Do
        End If
    Loop While r.Row * 10 + r.Column > nPrev
    
    On Error GoTo 0
    Exit Sub
Woops:
    Range("A1").Select
End Sub

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[DanUK86]

Thank you very much. i can stop pullin my hair out now lol

 

[SkipVought]

Don't do THAT!

Hair today, gone tomorrow! ;-)

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[samicbc]

Hi SkipVought and DanUK86,

I'm trying to reuse this code, and it doesn't seem to be working for me.

here is the exact code I'm using:

Sub ClearHighlights()
    Cells.Interior.ColorIndex = xlNone 'or choose a colorindex - use this code to clear all also

End Sub
Sub Button1_Click()
    Dim r As Range, SearchString As String, nPrev
    SearchString = Application.InputBox("What are you looking for?")
    On Error GoTo Woops
    nPrev = 0

    ClearHighlights

    Set r = Range("A5:F1000").Find(What:=SearchString)
    Do
        If Not r Is Nothing Then
            With r
                .Interior.ColorIndex = 22
            End With
            nPrev = r.Row * 10 + r.Column
            Set r = Range("A5:F1000").Find(What:=SearchString, After:=r)
        Else
            Exit Do
        End If
    Loop While r.Row * 10 + r.Column > nPrev
    
    On Error GoTo 0
    Exit Sub
Woops:
    Range("A1").Select
End Sub

I'm assigning the "Button1_Click()" macro to the button in the excel sheet. See the photo attached.

So for example, when I search for "blah", nothing gets highlighted

Please help! This is perfect for my application.

 

[SkipVought]

samicbc,

Please post your question in a new thread.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[xlbo]

using my telepathetic powers, I deduce that you have used a "Controls Toolbox" button. That being the case, set the "TakeFocusOnClick" property to FALSE

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DanUK86]

Your correct, it doesnt work. it just leaves the cells highlighted. All ive done is recorded a macro that clears all those highlight cells. Im sure theres a way that when you ask it to save the sheets it should clear the highlight cells back to the awau background i use on all my sheets

 

[SkipVought]

Huh???

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[DanUK86]

What ya mean huh? that coding you provided highlights all the cell it finds but what i asked for it to do was maybe clear the highlighted cells back to the normal aqua colour after maybe 10 mins of when the user saves so theres not random red cells everywhere where previous searches have been performed but i obviously want to keep the data in the cells

 

[SkipVought]

Sub ClearHighlights()
    Cells.Interior.ColorIndex = xlNone 'or choose a colorindex - use this code to clear all also

End Sub

Feel free to use this code that I provided on 4 Oct 07, in any way that you like.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[SkipVought]

try the Workbook_BeforeSave event.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[DanUK86]

Sorry but i dont know the first thing about VBA i wouldnt know how to use the workbook_beforesave thingy. Also included the clearhighlights but it doesnt work for me, maybe ive done it wrong but i just copied and pasted the whole list of coding into my spreadsheet. Sorry if im being trouble. If so ill just leave it as it is.