Getting a cell address from Vlookup function in a macro?

  • Thread starter Thread starter mickle026
  • Start date Start date
M

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
 
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
 
Back
Top