Getting a cell address from Vlookup function in a macro?

[mickle026]

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

 

[Don Guillett]

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

 

[mickle026]

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

 

[Don Guillett]

glad to help