I think Patrick meant you should paste values, then paste formats

Dim x As Range
x.PasteSpecial xlPasteValues
x.PasteSpecial xlPasteFormats

There is no xlAllValues and xlPasteAll is the same as a normal copy and
paste

XlPasteType constants: xlPasteAll, xlPasteFormulas, xlPasteValues,
xlPasteFormats, xlPasteNotes, or xlPasteAllExceptBorders.

--
Regards,
Tom Ogilvy


"Patrick Molloy" wrote in message
...
pastespecial xlAllValues pastes both format and value
you could just paste special the format to leave the
values unchanged but to render the format.

Dim x As Range
x.PasteSpecial xlPasteAll
x.PasteSpecial xlPasteFormats

Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
Thanks for the response to my last posting. This one I
hope is even simpler. How can I cut and paste the
colour
formatting of a cell and its value? The value is
generated by a formula so normal cut and paste doesn't
work and a paste special value transfer removes the
colour. Any ideas?

Thanks,

Bob
.