Linking to an active cell

[Sandip]

Hi,

I have a range of cells say from A1 to A20 having certain numbers. I
would like the output of any of these cell range in C1 depending on
what the active cell is. The active cell will always be one cell and
not a range of cells.

For eg, If the active cell is A5, the value of A5 should appear in C1.
As the person presses the down arrow and scroll downs the list, the
number in C1 should change accordingly.

The output in C1 is supposed to drive various Vlookup formulas.

If the active cell is no longer within the A1 to A20 range, the last
selected cell in A1 to A20 should remain in C1.

Appreciate anyone's help..

Thanks
Sandip.

 

[Nigel]

Put this code into the worksheet module ... (right click worksheet tab
select View Code)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 And Target.Row >= 1 And Target.Row <= 20 Then
Range("C1") = ActiveCell.Value
End If
End Sub

 

[Simon Lloyd]

This goes in worksheet code page, after you have entered a value if you
move the cursor back to the cell the value is displayed in c1, i didnt
have time to test it properly but it should work!

HTH

Simon

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Range("A1:A20")
For Each cell In rng
Range("C1").Value = ActiveCell.Value
Next

End Sub

 

[Sandip]

Hi Simon,

I have used your code and it does work however I have one problem. The
below macro is picking up the all the active cells and copying it in J5
even if its not within the range B8:B30.

What extra code do I need to add so that if any cell other than the
range is active, the same should not be pasted in J5.

Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Range("b8:b30")
For Each cell In rng
Range("j5").Value = ActiveCell.Value
Next
End Sub

Regards
Sandip.

 

[Dnereb]

Go with Nigel's code it's better