VBA event handler help for Excel

[AshikHusein]

I am a very new VBA programmer for Excel. I would like to know how to do the followinG

I am using a worksheet_Change event handing procedure.

When a cell is changed the active cell is changed depending on which key you use. (ie if you use the right arrow key, the active cell is one cell right to the changed cell)

I want Excel to remember the changed cell so that I can use the address of the changed cell to reference other cells. I would like to know a way to program this.

Will really appreciate help for the above, Thanks.

 

[Fornatian]

This isn't difficult if you use a public variable to store a range reference. What are you trying to achieve?

'place the variable declaration outside any procedures to ensure it retains it value between calls.

Public stRange As String

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Msgbox "Old Value was" & stRange
stRange = Target.AddressLocal
Msgbox "New Value is" & stRange
End Sub

 

[AshikHusein]

Thank You! That solved my problem.

I was trying to write a subroutine which checked a column for duplicate entries, when a cell in that column was changed or a new one entered. Thanks again.