How the get the column or row number of a changed cell on Worksheet_Change condition?

[minmark]

Dear Gents,

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B2:B5")) Is Nothing Then
       Cancel = True    
ActCol = Activecell.Column
 'other macro

According to above codes, If the user change the value of B2 to B5 and press "tab" on keyboard
, the activecell.column will be 2. But if the user click other cells instead of press tab, the activecell.column
will not be 2.

Please help me to figure out how to get the changed cell's row or column if user didn't press "tab" key in this condition.

Thanks.

 

[Yongle]

try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Long, r As Long
    If Not Application.Intersect(Target, Range("B2:B5")) Is Nothing Then
        c = Target.Column:  r = Target.Row
        MsgBox "column " & c & vbCr & "row " & r, , "Amended Cell..."
    End If
End Sub

 

[Peter_SSs]

Please help me to figure out how to get the changed cell's row or column if user didn't press "tab" key in this condition.

Given that you are using

If Not Application.Intersect(Target, Range("B2:B5")) Is Nothing Then

then the column of the changed cell(s) in the range B2:B5 would always be 2.

If you were looking for the row of the changed cell (& assuming only 1 cell was changed), then use something like

ActRow = Target.Row

 

[minmark]

Hi, Mr. Peter.
It really works.
Thanks for the detailed explanation.

 

[minmark]

Hi, Yongle.
Thanks for the post, you answer also show me how to use msgbox.
Thanks.

 

[Peter_SSs]

Hi, Mr. Peter.
It really works.
Thanks for the detailed explanation.

You are very welcome - glad it worked for you and thanks for letting us know.