Dragging a formula from a active cell - VBA - VLOOKUP

[The Student]

Hey all you cool excel-shark out there.

I got a sheet, where i like to do a VLOOKUP from a active cell.

The thing is that the active cell can change from time to time and I like
the VBA-code to make a VLOOKUP from that active cell and then dragging the formula below to a certain row.

How do I drag it from the active cell in VBA????

And is it possible to automatically make it stop by coloring a cell in that column (the last row containing a VLOOKUP)

Hope you can help and understand my question