Converting code from static to dynamic

Hello All! I started writing Macros a couple weeks ago.
Currently I've managed to write lots of code without using "range" at all, using for example Cells(row, col) references instead.
Now I wish to transform my code from working on defined columns to whichever is selected.

I'm having a hard time transforming the following code ..

Range("L:L") = [index(Upper(L:L),)]

.. to using a Range defining the active(cell.)column but, with an offset of +1 (ie, I wish to target the column to the right of the currently selected one)
its the combo of 'range', 'activecell' and 'offset' which is throwing me off as I haven't made use of them previously.

Thank you very much for your time and patience !
Best regards,
Kris

This will target the entire column immediately to the right of the ActiveCell:

Formula:

Please Login or Register  to view this content.

.Offset(0,1) means Offset by 0 rows and 1 column.

@the two forum guru's, that line is supposed to change the entire column to Uppercase. Its a line I found on a site somewhere and it worked, I kept it as it saved me making a For loop.
In my quest to making the code "dynamic", this was the only line of my code that I didn't know how to "translate".

If you have another (vba) formula that can achieve the same result (make the column next to the selected one ALL CAPS) I'd be more than willing to welcome your input :D

hey! thanks so much for all your replies. Unfortunately I can't get it to work. the best i achieve is getting #VALUE! as cell value
I know I've got the correct left-hand-side of the equation, its something with the right-hand-side which is not wanting to cooperate.
There has to be a way to write "[index(Upper(L:L),)]" generically (as reminder, the L:L range should be replaced by something resembling activecell.entirecolumn to have it use the selected column)

Also, still on topic, does anyone have any input on UCASE vs UPPER methods? I tried looking for it online but couldn't find a site which combined both.

Capture11.PNG
Well for some reason it just won't want to work with me, please see screenshot, and it won't even do the whole column.

ianquiksilver
Change to

Please Login or Register  to view this content.