Find column of value changed

[tiredofit]

I want to return the column of a cell whose value has changed.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  
    For Each Target In Selection
      
        MsgBox Target.Column
  
    Next Target
  
End Sub

If I enter a value into cell A1, (say 20), then press Enter (assuming the default setting is set to "After pressing Enter, move selection Down"), the message box displays 1, as expected.

However, if I enter a value into cell A1, (say 30), then clicked onto a cell on another column, say cell E6, the message box displays 5 (because E is the fifth column) despite cell A1 (correctly) shows a value of 30.

Similarly, if I enter a value into cell A1, (say 50), then clicked the TAB key, the message box shows 2 (because now the active cell is B1).

How can I amend my code so in these examples, it always displays 1?

Thanks

 

[tiredofit]

I want to return the column of a cell whose value has changed.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
 
    For Each Target In Selection
   
        MsgBox Target.Column
 
    Next Target
 
End Sub

If I enter a value into cell A1, (say 20), then press Enter (assuming the default setting is set to "After pressing Enter, move selection Down"), the message box displays 1, as expected.

However, if I enter a value into cell A1, (say 30), then clicked onto a cell on another column, say cell E6, the message box displays 5 (because E is the fifth column) despite cell A1 (correctly) shows a value of 30.

Similarly, if I enter a value into cell A1, (say 50), then clicked the TAB key, the message box shows 2 (because now the active cell is B1).

How can I amend my code so in these examples, it always displays 1?

Thanks

I've experimented and found this works:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

        MsgBox Target.Column
   
End Sub

but the reason I added the For Next Loop is in case users selected more than a single cell.

Is there a workaround for that scenario?

 

[Fluff]

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng As Range
   
   For Each Rng In Target
      MsgBox Rng.Column
   Next Rng
End Sub

 

[tiredofit]

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng As Range
  
   For Each Rng In Target
      MsgBox Rng.Column
   Next Rng
End Sub

Thanks, that does exactly what I want.

 

[Fluff]

You're welcome & thanks for the feedback.