Getting a cell address from Vlookup function in a macro?

Does anyone know how to get the cells address from a vlookup funtion in a macro, Im relatively new to excel and learning fast, but keep comin up against brick walls!

What I have done, is a lookup sheet where i type in a number and if its found in a table in another sheet, it returns values from other columns, ie name/address, but i want to be able to select the name from my lookup sheet and mark it by painting the cell, the painting bit i can do, i just cant figure out how to get the cells address from the vlookup function, any ideas?

ie,

Sheets("Lookup").Select
Range("G5").Select ' my lookup value is in this box
' c[-6]:c[3] = A:J
ActiveCell.FormulaR1C1 = "=VLOOKUP(R5C7,'Phone List'!C[-6]:C[3],6,FALSE)"

' i have place a button on the sheet with this code:

Sheets("Phone List").Select
'Range(" --- here i need an address --- ").Select
ActiveCell.Select
' paint yellow
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
' go back to lookup sheet
Sheets("Lookup").Select


please help me .... thanks

wouldn't this be easier? Modify to suit. NO selections necessary. Works with
your cursor anywhere in the workbook

Sub colorvlookupcell()
whattofind = Sheets("sheet17").Range("g5")
Sheets("sheet18").Columns(1).Find(whattofind) _
.Offset(, 1).Interior.ColorIndex = 6
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"mickle026" <[email protected]> wrote
in message news:[email protected]...
>
> Does anyone know how to get the cells address from a vlookup funtion in
> a macro, Im relatively new to excel and learning fast, but keep comin
> up against brick walls!
>
> What I have done, is a lookup sheet where i type in a number and if its
> found in a table in another sheet, it returns values from other columns,
> ie name/address, but i want to be able to select the name from my lookup
> sheet and mark it by painting the cell, the painting bit i can do, i
> just cant figure out how to get the cells address from the vlookup
> function, any ideas?
>
> ie,
>
> Sheets("Lookup").Select
> Range("G5").Select ' my lookup value is in this box
> ' c[-6]:c[3] = A:J
> ActiveCell.FormulaR1C1 = "=VLOOKUP(R5C7,'Phone
> List'!C[-6]:C[3],6,FALSE)"
>
> ' i have place a button on the sheet with this code:
>
> Sheets("Phone List").Select
> 'Range(" --- here i need an address --- ").Select
> ActiveCell.Select
> ' paint yellow
> With Selection.Interior
> ColorIndex = 6
> Pattern = xlSolid
> End With
> ' go back to lookup sheet
> Sheets("Lookup").Select
>
>
> please help me .... thanks
>
>
> --
> mickle026
> ------------------------------------------------------------------------
> mickle026's Profile:
> http://www.excelforum.com/member.php...o&userid=35518
> View this thread: http://www.excelforum.com/showthread...hreadid=552904
>

Thank You very much, sure beats searching the sheet with a for next loop!

glad to help

--
Don Guillett
SalesAid Software
[email protected]
"mickle026" <[email protected]> wrote
in message news:[email protected]...
>
> Thank You very much, sure beats searching the sheet with a for next
> loop!
>
>
> --
> mickle026
> ------------------------------------------------------------------------
> mickle026's Profile:
> http://www.excelforum.com/member.php...o&userid=35518
> View this thread: http://www.excelforum.com/showthread...hreadid=552904
>