active cell reference

[beans4brekkie1]

Hi,
I was looking into how to reference the active cell and found a macro.

How do I then make this run within a cell

eg: I want cell A1 to always have the cell reference of the currently active (highlighted) cell. then i can use this to reference in other formulas.

How do i do this?

thanks

 

[Richard Schollar]

You'd need code like this (in the sheet's code module - right-click on the sheet name and select View COde - paste in here):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("A1").Value = ActiveCell.Address(False,False)

End Sub

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = Target.Address
End Sub

 

[Richard Schollar]

I'm being pedantic here, but Target is not necessarily the same as the Activecell

 

[VoG]

True enough ... very pedantic

 

[beans4brekkie1]

Hi, again.

This works brilliantly - now to improve the macro...

if i select a merged cell - it is the range that comes up in cell A1 (ie B2:C3 for example). Is there a way to make it only come up with B2 (as this is surely the actual name for the cell once its merged?)

Thanks

 

[Richard Schollar]

You just need this minor modification:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Range("A1").Value = ActiveCell.MergeArea(1).Address(False, False)

End Sub