Dynamically update specific cell value based on active cell-CodePudding

Example: I want to dynamically update cell A1 when I am using the down arrow to move down a range of B1:B10.

For example, if I have 1 - 10 in cells B1:B10 respectively, cell A1 will always update to the contents of whichever cell is active within that specified range.

My current code is this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

     If Not (Intersect(ActiveCell, Range("$B$1:$B$10")) Is Nothing) Then
          Cells(1, 1).Value = Cells(Selection.Row, 1)
     End If

End Sub

This code does not update the cell with the value of the active cell. However, if I enter a number in A1, then select any cell in the specified range, it deletes the contents of cell A1.

My knowledge of VBA is pretty limited, so if you have an answer to my problem, I would greatly appreciate an explanation to how it works.

CodePudding user response：

Just change the number for the column at the third line... I've just tried your code, and it works perfectly. The only thing wrong is the column at the third line.

Cells(Selection.Row, COLUMN 2!)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If Not (Intersect(ActiveCell, Range("$B$1:$B$10")) Is Nothing) Then
      Cells(1, 1).Value = Cells(Selection.Row, 2) '<--- CHANGE IT HERE
 End If

End Sub

Or you could try

Range("A1") = Range("B" & Target.Row)

instead of

Cells(....)

for the whole third line.