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

[dwyborn]

Hi,

I have a spreadsheet that contains a list of cheques that I have poste
to clients. When these cheques are cashed or cancelled I highlight th
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 tha
I have edited.

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

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

Thanks

For your help.

 

[Tom Ogilvy]

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

 

[dwyborn]

Thanks this works great