How do I edit a selected range then copy the range into an new sheet???

Hi,

I have a spreadsheet that contains a list of cheques that I have posted to clients. When these cheques are cashed or cancelled I highlight the selected line in a colour and type cashed or cancelled in column J. In a seperate sheet I then create a list of all the cheques lines that I have edited.

What I would like is a macro that does this for me: I.e highlights the current line and turns the font colour purple, types cancelled in column J and copies the contents of cell D to the bottom of the list on the updates sheet.

I've been experienmenting on my own but can't get excel to copy the contents correctly.

Thanks

For your help.
D

Sub CancelCheque()
Dim rng as Range, rng1 as Range
set rng = ActiveCell.entireRow
rng.font.ColorIndex = 39
set rng1 = worksheets("Cancelled").Cells( _
rows.count,"j").end(xlup).offset(1,-9)
cells(rng.row,"j").Value = "Cancelled"
rng.copy
rng1.PasteSpecial xlValues
rng1.PasteSpecial xlFormats
End Sub

if you wanted to do it for multiplecells. select one cell in each of the
rows and then do (this is a more generalized approach and will work for a
single cell as well)

Sub CancelCheques()
Dim rng as Range, rng1 as Range
Dim rng2 as Range, cell as Range
set rng2 = Intersect(selection.EntireRow,Columns(1))
for each cell in rng2
set rng = cell.EntireRow
rng.font.ColorIndex = 39
set rng1 = worksheets("Cancelled").Cells( _
rows.count,"j").end(xlup).offset(1,-9)
cells(rng.row,"j").Value = "Cancelled"
rng.copy
rng1.PasteSpecial xlValues
rng1.PasteSpecial xlFormats
Next
End sub

--
Regards,
Tom Ogilvy

"dwyborn" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I have a spreadsheet that contains a list of cheques that I have posted
> to clients. When these cheques are cashed or cancelled I highlight the
> selected line in a colour and type cashed or cancelled in column J.
> In a seperate sheet I then create a list of all the cheques lines that
> I have edited.
>
> What I would like is a macro that does this for me: I.e highlights the
> current line and turns the font colour purple, types cancelled in
> column J and copies the contents of cell D to the bottom of the list on
> the updates sheet.
>
> I've been experienmenting on my own but can't get excel to copy the
> contents correctly.
>
> Thanks
>
> For your help.
> D
>
>
> --
> dwyborn
> ------------------------------------------------------------------------
> dwyborn's Profile:
http://www.excelforum.com/member.php...o&userid=29695
> View this thread: http://www.excelforum.com/showthread...hreadid=494102
>

Thanks this works great :o)