Returning value of top row of active cell

[Damo71]

Hi

When I select a cell, how do I programmatically, in Cell A1, return the value of the third row in whatever column the current selection is? (it will never be in column A)

E.g. H3 contains the value "Pickles". When I select H15 (or any other row in H), cell A1 will return "Pickles".

However if the current selection is in Row 3, the macro doesn't run

thanks

 

[Legacy 143009]

Try an if statement before returning values.

For example;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If ActiveCell.Row <> 3 Then[COLOR=#333333]
[/COLOR]      Cells(1, 1).Value = Cells(3, ActiveCell.Column).Value
   Else
       'Do nothing maybe? Or else?
   End If
End Sub

But in my opnion only

Cells(1, 1).Value = Cells(3, ActiveCell.Column).Value

line should be enough

 

[Fluff]

Another option

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.row = 3 And Not Target.column = 1 Then
      Range("A1").Value = Cells(3, Target.column).Value
   End If

End Sub

This prevents any problems incase more than 1 cell is selected.