How to refer back to previously populated cell during change event

[DwinZly]

Hello,

I have a basic change event macro here:

Private Sub Worksheet_Change(ByVal Target As Range)

If Selection.Count = 1 Then
If Not Intersect(Target, Me.Range("B3:B300")) Is Nothing Then AutoFill
End If


End Sub

The problem is that I want to be able to refer back to the cell that was just changed in my AutoFill macro. For instance, if someone changes the value in cell B5, they can either hit enter (to B6) or arrow over (to C5) and the final destination of the cursor will be different each time. Therefore I cannot accurately predict what the cell was that was just changed. Unfortunately the change event does not trigger the macro until the cursor has moved so I can't use activecell commands. Is there a way to run the macro after the value changes but before the cursor moves?

Thanks

 

[Fluff]

The cell that was changed is Target
HTH

 

[Rick Rothstein]

The problem is that I want to be able to refer back to the cell that was just changed in my AutoFill macro. For instance, if someone changes the value in cell B5, they can either hit enter (to B6) or arrow over (to C5) and the final destination of the cursor will be different each time. Therefore I cannot accurately predict what the cell was that was just changed. Unfortunately the change event does not trigger the macro until the cursor has moved so I can't use activecell commands. Is there a way to run the macro after the value changes but before the cursor moves?

Create a module level global variable (Dim it at the top of the code module outside of any procedures) as a Range variable and set the variable to reference the Target in the SelectChange event procedure. That way, you can use the variable inside of the Change event procedure (but do not Dim it inside the Change event procedure, only Dim it outside of any procedures at the top of the module).

 

[DwinZly]

Thanks Rick