How to refer to the previous target

  • Thread starter Thread starter Damien McBain
  • Start date Start date
D

Damien McBain

Hi,
Probably a dumb question... I'm changing the colour of cells based on what
the user inputs (too many options for conditional formatting).
Within the "worksheet change" event, I know that "target" is the cell the
user is moving to. How do I reference the cell the user is moving from?
TIA
Damien
 
We need to remember where we came from:

Dim WhereWasI As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If WhereWasI = "" Then
WhereWasI = Target.Address
End If
MsgBox ("we came from " & WhereWasI)
MsgBox ("we now reside at " & Target.Address)
WhereWasI = Target.Address
End Sub
 
How do I reference the cell the user is moving from?

Hi. Just another idea is to have Excel internally remember where we were.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Goto Target
End Sub

Then, you access the address via...

Function PreviousAddress()
PreviousAddress = Application.PreviousSelections(2).Address
End Function


Sub WhereWasIQ()
MsgBox PreviousAddress
End Sub


Note that if you want to "Go Back" to where you were, don't forget to
turn off Events...

Sub GoBack()
With Application
.EnableEvents = False
.Goto Range(.PreviousSelections(2).Address)
.EnableEvents = True
End With
End Sub

= = = = = = = = = = = = =
HTH
Dana DeLouis
 
Back
Top