one click selection

[C.S.]

If I have a worksheet with a range of cells (ex. A1:G10) containing dates, is there any way I can setup the worksheet to return the value whatever cell I click on (one click or double click) to another specified cell? Example: Say I've designated F1 to be the cell I want the selected value to return to. I click on cell A1 (which contains 1/1/2010) it will return that value to cell F1.

Is this possible? I'm imagining something similar to a "little hand" selection icon.

Also, if so, can this be done without using macros. And if not, will the macro be usable in both excel 2003 and 2007?

 

[Jonmo1]

1. No, can't be done without Macros
2. Yes, this will work in 2003 and 2007

I designated J1.
It didn't make sense to me for the designated cell(F1) to be within the target range A1:G10

Right click the sheet tab, click View code
Paste the following

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:G10")) Is Nothing Then
    Range("J1").Value = Target.Value
End If
End Sub

 

[C.S.]

Glad you knew what I meant. Thanks so much.

 

[VoG]

Try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 1 Then
    Cancel = True
    Target.Offset(, 5).Value = Target.Value
End If
End Sub