highlight the matching row - vlookup vba

[lezawang]

Hi
I have a table with 2 columns, Name and Phone# column. I wrote vlookup code (see below) and it is working fine. I want to modified it so that excel will highlight the row that contains that name and phone number. Is that possible? Thank you so much.
++

Sub price()
Dim item As String
Dim x As String
item = InputBox("enter Name")
Sheets("price").Activate
x = WorksheetFunction.VLookup(item, Range("A2:B171"), 2, False)
MsgBox item & " phone# is --> " & x


End Sub

 

[mumps]

Try:

Sub price()
    Dim item As String
    Dim rng As Range
    With Sheets("price")
        .Range("A2:B" & .Range("B" & .Rows.Count).End(xlUp).Row).Interior.ColorIndex = xlNone
    End With
    item = InputBox("Enter Name")
    Set rng = Sheets("price").Range("A:A").Find(item, LookIn:=xlValues, lookat:=xlWhole)
    If Not rng Is Nothing Then
        rng.EntireRow.Interior.ColorIndex = 3
    Else
        MsgBox ("Name not found.")
    End If
End Sub

 

[lezawang]

Thank you so much, it works amazing. It did highlight all the row. Sorry for bothering you, is it possible to highlight only the 2 cells in that row, the one has the Name and the Phone number? Thank you once again for this great help.

 

[mumps]

Try:

Sub price()
    Dim item As String
    Dim rng As Range
    With Sheets("price")
        .Range("A2:B" & .Range("B" & .Rows.Count).End(xlUp).Row).Interior.ColorIndex = xlNone
    End With
    item = InputBox("Enter Name")
    Set rng = Sheets("price").Range("A:A").Find(item, LookIn:=xlValues, lookat:=xlWhole)
    If Not rng Is Nothing Then
        rng.Resize(, 2).Interior.ColorIndex = 3
    Else
        MsgBox ("Name not found.")
    End If
End Sub

 

[lezawang]

Wow, works beautifully. Thank you so much indeed.

 

[mumps]

My pleasure.