Search and highlight box

[trevolly]

Hi all

I currently have a worksheet named "Database" with multiple fields of data on it, spread across columns "A" to "BG". Id like to be able to have a "searchbox" which when you type a value in to it will search for that SPECIFIC value in two of the columns in the worksheet and then only show that row.

One of the column is "A" and has a basic number (which is used as an order identifier) ie: 1, 2, 3 etc... the other column "B" has the date in a "dd/mm/yyyy" format. So I'd either be typing a number or a date.

Id like to be able to search for either of these and it only display the relevant row.

Is this possible?

 

[JoeMo]

Untested, try on a copy of your database sheet:

Sub FindAndHighlight()
'run from sheet "Database"
Dim Fnd As Variant, R As Range
Fnd = InputBox("Enter an order number as an integer or a date in format dd/mm/yyyy")
If Fnd = "" Then Exit Sub 'user clicked cancel
If InStr(Fnd, "/") > 0 Then
    Set R = Range("B:B").Find(DateValue(Fnd), LookIn:=xlFormulas, lookat:=xlWhole)
    If R Is Nothing Then
        MsgBox "The date you entered could not be found in column B"
        Exit Sub
    Else
        Cells(R.Row, "A").Resize(1, 59).Select
    End If
Else
    Set R = Range("A:A").Find(Fnd, LookIn:=xlFormulas, lookat:=xlWhole)
    If R Is Nothing Then
        MsgBox "The order # you entered could not be found in column A"
        Exit Sub
    Else
        Cells(R.Row, "A").Resize(1, 59).Select
    End If
End If
End Sub

 

[trevolly]

Thats great - that works like a dream! I really appreciate the time and effort. Thank you so much @JoeMo

 

[JoeMo]

Thats great - that works like a dream! I really appreciate the time and effort. Thank you so much @JoeMo

You are welcome - thanks for the reply.

 

[trevolly]

Hey - using this vba code...(which works perfectly) would it be possible to either highlight the result in a colour or so that the result is the only row shown?

 

[JoeMo]

Hey - using this vba code...(which works perfectly) would it be possible to either highlight the result in a colour or so that the result is the only row shown?

Change highlight color (in two places) to suit:

Sub FindAndHighlight()
'run from sheet "Database"
Dim Fnd As Variant, R As Range
Fnd = InputBox("Enter an order number as an integer or a date in format dd/mm/yyyy")
If Fnd = "" Then Exit Sub 'user clicked cancel
If InStr(Fnd, "/") > 0 Then
    ActiveSheet.UsedRange.Interior.Pattern = xlNone
    Set R = Range("B:B").Find(DateValue(Fnd), LookIn:=xlFormulas, lookat:=xlWhole)
    If R Is Nothing Then
        MsgBox "The date you entered could not be found in column B"
        Exit Sub
    Else
        With Cells(R.Row, "A").Resize(1, 59)
            .Select
            .Interior.Color = vbYellow
        End With
    End If
Else
    ActiveSheet.UsedRange.Interior.Pattern = xlNone
    Set R = Range("A:A").Find(Fnd, LookIn:=xlFormulas, lookat:=xlWhole)
    If R Is Nothing Then
        MsgBox "The order # you entered could not be found in column A"
        Exit Sub
    Else
        With Cells(R.Row, "A").Resize(1, 59)
            .Select
            .Interior.Color = vbYellow
        End With
    End If
End If
End Sub

 

[trevolly]

Thank you very @JoeMo. Sorry I didn’t reply sooner… my Nan died so I have been tied up. Thank you.

 

[JoeMo]

Thank you very @JoeMo. Sorry I didn’t reply sooner… my Nan died so I have been tied up. Thank you.

You are welcome and please accept my condolences on the death of your Nan.