macro to copy & paste cell data into another cell

Hello;
I want to copy & paste the cell data when any cell in the worksheet
gets selected. I would like to have the data stored in a list either on the
same worksheet or in another worksheet. I have the selected cells background
color change when selected but having trouble putting the cells contents into
another cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng as Range, rng1 as Range, col as Long
set rng = Target(1)
cell.Interior.ColorIndex = xlnone
rng.Interior.ColorIndex = 5
col = worksheets("sheet2").Cells(1,256).End(xltoleft).column
set rng1 = worksheets("sheet2").Cells(rows.count,col).End(xlup)
if not isempty(rng1) then set rng1 = rng1.offset(1,0)
if rng1.row > 65000 then
col = col + 1
set rng1 = Worksheets("Sheet2").Cells(1,col).value
end if
rng1.value = rng.Value
End Sub

--
Regards,
Tom Ogilvy

"dannyboy" <dannyboy@discussions.microsoft.com> wrote in message
news:F547F446-2B9E-4447-BF0D-F7D54710429B@microsoft.com...
> Hello;
> I want to copy & paste the cell data when any cell in the
worksheet
> gets selected. I would like to have the data stored in a list either on
the
> same worksheet or in another worksheet. I have the selected cells
background
> color change when selected but having trouble putting the cells contents
into
> another cell.
>

Thanks!!!
Works Great.


"Tom Ogilvy" wrote:

> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim rng as Range, rng1 as Range, col as Long
> set rng = Target(1)
> cell.Interior.ColorIndex = xlnone
> rng.Interior.ColorIndex = 5
> col = worksheets("sheet2").Cells(1,256).End(xltoleft).column
> set rng1 = worksheets("sheet2").Cells(rows.count,col).End(xlup)
> if not isempty(rng1) then set rng1 = rng1.offset(1,0)
> if rng1.row > 65000 then
> col = col + 1
> set rng1 = Worksheets("Sheet2").Cells(1,col).value
> end if
> rng1.value = rng.Value
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "dannyboy" <dannyboy@discussions.microsoft.com> wrote in message
> news:F547F446-2B9E-4447-BF0D-F7D54710429B@microsoft.com...
> > Hello;
> > I want to copy & paste the cell data when any cell in the
> worksheet
> > gets selected. I would like to have the data stored in a list either on
> the
> > same worksheet or in another worksheet. I have the selected cells
> background
> > color change when selected but having trouble putting the cells contents
> into
> > another cell.
> >
>
>
>