Cell Data to Reflect Active Cell

P

Peter Davies

Help - I'm struggling! Is it possible to switch the data in a particular
cell to reflect the active cell as and when I move through a range in my
spreadsheet?

For example:

If Cells:
A1 = Apples
A2 = Pears
A3 = Oranges
A4 = Lemons

and my active cell is A1 then I would like B5 to = Apples. If I move focus
so that the Active cell is now A3 I then I would like B5 to = Oranges and so
on.

Thanks in advance, Peter
 
D

Dodo

Help - I'm struggling! Is it possible to switch the data in a
particular cell to reflect the active cell as and when I move through
a range in my spreadsheet?
Click to expand...

No, but just to satisfy my curiosity: Why would you need that?

The cell contents are visible?
 
H

hideki

Is this you are looking for?

You can use target at the worksheet changing event. Below is the
example of it. When you click at A1, cells D1 will show the value of
A1. If you click on A2, cells D1 will show the value of A2.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Target
Case Cells(1, "A")
Cells(1, "D") = Target.Value
Case Cells(2, "A")
Cells(1, "D") = Target.Value
End Select

End Sub
 
E

Earl Kiosterud

Peter,

You could use this proc. Put it in the sheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Range("B5") = ActiveCell.Value
End Sub

If this should happen only when a cell is selected in Column A, then:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
ActiveSheet.Range("B5") = ActiveCell.Value
Else
Range("B5").ClearContents
End If
End Sub

These won't work when the user has selected a range and is going through it
with Enter or Tab. It also won't work if the user has selected more than
one cell, then reselects the same range but with a different active (white)
cell.
 
Top