Return cell reference of another cell thats highlighted

[pleased2help]

Is there a formula in Excel 2010 to return the cell ref of another highlighted cell ie:

If I move the cursor to cell "C1" and enter to select it

cell "B1" would show "C1" or better still the value in cell "C1"

If I then move the cursor to cell "C2" and enter to select it

cell "B1" would show "C2" or better still the value in cell "C2"

I would only need to do this for a range of cells "C1 to C14"

Many thanks Paul

[NBVC]

There is no formula to do that... but you can use VBA.

right click on the sheet tab and select View Code.

paste the following code in the editor.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target.Cells, Range("C1:C14")) Is Nothing Then Exit Sub
    Target.Offset(0, -1) = Target.Value
End Sub

now select an item in C1:C14 range and B1 should change accordingly.

[pleased2help]

There is no formula to do that... but you can use VBA.

right click on the sheet tab and select View Code.

paste the following code in the editor.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target.Cells, Range("C1:C14")) Is Nothing Then Exit Sub
    Target.Offset(0, -1) = Target.Value
End Sub

now select an item in C1:C14 range and B1 should change accordingly.

Help please:-
The cell next to the selected cell has the value in ie, if i select c1 then b1 has value, if i select c3 then b3 has the value, I just neet the value of the selected cells to appear in "B1" only . I would this to run all the time while the work book is open for any selection in the range C1:C14
Thanks

[NBVC]

Just simply change to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target.Cells, Range("C1:C14")) Is Nothing Then Exit Sub
    Range("B1").Value = Target.Value
End Sub

[pleased2help]

Just simply change to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target.Cells, Range("C1:C14")) Is Nothing Then Exit Sub
    Range("B1").Value = Target.Value
End Sub

Thank you for your help, much appreciated. Works great

Re the:-
Range("B1").Value = Target.Value

Can i get the result to another spreadsheet in the same workbook ie if it was just excel the reference for the result would be: " 'LOOKUP '!B1 "
"LOOKUP" BEING THE WORK SHEET B1 THE CELL REF.

[NBVC]

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target.Cells, Range("C1:C14")) Is Nothing Then Exit Sub
    Sheets("Lookup").Range("B1").Value = Target.Value
End Sub