Cell Data to Reflect Active Cell

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

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

"Peter Davies" <[email protected]> wrote in
news:#[email protected]:

> 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?
>

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

The cell contents are visible?


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

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.

--
Earl Kiosterud
www.smokeylake.com

"Peter Davies" <[email protected]> wrote in message
news:%[email protected]...
> 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
>