Run Macro on SelectionChange referencing updated cell

[mexmanz]

Good Day

I have a short macro to run when a cell is changed in column 2.
This works, however it only executes when re-selecting the cell after any changes, rather than running when moving away from cell.

How can I get this macro to run referencing the updated cell, when moving away from this active cell?

I believe I would use ActiveCell.Address to back reference and run somehow, but cant figure it out!

---------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ActiveCell.Column <> 2 Then Exit Sub
If IsNumeric(Target) Then
If ActiveCell > 0 Then
ActiveCell.Offset(0, 1).IndentLevel = 2
End If
End If
End Sub



Thank you
Andrew

 

[pgc01]

Hi Andrew

You usually don't use the ActiveCell range in event procedures.

The parameter Target of the event procedure refers to the cell(s) that fired the event.

In an event procedure you'd use something like

If Target.Column <> 2 Then Exit Sub

 

[Akuini]

Instead of using "Private Sub Worksheet_SelectionChange" you should use "Private Sub Worksheet_Change".

 

[mexmanz]

Hi Akuini

Thank you for your reply,

I have adjusted the Sub type, this now executes when confirming or moving away form the cell being changed.

However the problem remains that it is not referencing this same cell that has just been changed, it references the cell being selected after the change.

How can the following be setup:

Select cell B4
Change value to 5
Press Enter/Arrow keys or click to other cell
Now cell C4 should change to IndentLevel = 2


Many thanks

 

[Akuini]

I don't quite understand what you want, but maybe this:

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Columns([COLOR=crimson]2[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        [COLOR=Royalblue]If[/COLOR] Target.Cells.Count = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR]
            [COLOR=Royalblue]If[/COLOR] IsNumeric(Target) [COLOR=Royalblue]And[/COLOR] Target.Value > [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
            Target.Offset([COLOR=crimson]0[/COLOR], [COLOR=crimson]1[/COLOR]).IndentLevel = [COLOR=crimson]2[/COLOR]
            [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[mexmanz]

That is perfect Akuini, thank you so much
I will study it more to get clarity on it

All the best

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.