Macro for searching a spreasheet for text/value/.

[emondragon07]

All,

I'm putting to together a spreadsheet for material requisition for a construction project. These spreadsheets tend to grow very large. Sometimes I need to search for a specific item using a purchase number or description. The filters offer some help but it would be awesome if i could designate one cell where i could input a purchase number or text that searches the spreadsheet and highlights all the hits. Say, Cell N2 is to be my "search cell." can someone help this I would greatly appreciate it.

emondragon 07

[AB33]

Please attach a sample. It is doable

[emondragon07]

Yes please see attached document and thank you!

[AB33]

Emo,
I see you have attached a sample. I also see you have data in sheet "SHEET METAL". Now, you want to search for a PO. Where? Is it in this sheet only. You have a PO in column G, Or do you wish to search across multisheets?

[emondragon07]

AB33,

I would like to search for a PO or if someone wanted to, even search for words within the item description column if possible. I would like it to search just within the active sheet. So in this case just SHEET METAL. So essentially I would like the search to be flexible in what I choose to type. Lots of times there will be the same PO number for different material so in this case typing a search by item description would be better even just key words. If you can do both that would be awesome. If i need to provide more information I will. Thank you for the response.

[AB33]

Here is one way. The code highlightes the entire row. You can easily change the findme

For e,g you can set it like

findme= "Your stirng to search" . In this code, the findme is linked to a cell reference

Sub serachme()
  
Dim rFind As Range, Findme As String, sAddr As String, ws As Worksheet

Set ws = Sheets("SHEET METAL")

Findme = ws.Range("G6")


        With ws.UsedRange
            Set rFind = .Find(What:=Findme, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
            If Not rFind Is Nothing Then
                sAddr = rFind.Address
                Do
                    rFind.EntireRow.Interior.Color = vbRed
                    
                    Set rFind = .FindNext(rFind)
                Loop While rFind.Address <> sAddr
                sAddr = ""
            End If
        End With

End Sub