PasteSpecial with Conditional Formats

[pergo]

Hey All,

I was wondering if it's possible to have PasteSpecial paste conditional formats. I have a range that's background color changes when it's values are within a certain range. I am copying that range to an e-mail, but when it is pasted in the e-mail, the formatted color changes.

Is there anyway to copy the conditional formatted results as well?

Below is the code I have for the copying/pasting portion:

rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

Thanks!

 

[GlennUK]

Wouldn't copying as a picture be more suitable in that case?

 

[pergo]

Is that something possible? That would definitely work.

Could you direct me to how to code that?

Thanks! :D

 

[GlennUK]

It'd be like:

rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
    .Range("B16").Select
    .Paste
End With
Application.CutCopyMode = False

... not tested.

 

[pergo]

Great. Thanks! I'll be able to check and test this tomorrow. Just curious about the B16, was that random or for a reason?

 

[GlennUK]

Just random.

 

[pergo]

I just tested this and it didn't work. It doesn't seem to copy the range. And it pastes nothing. Any advice?

 

[mikerickson]

How are you copying to e-mail.
Copy from Excel, paste into Mail window?

If so, I doubt that the Mail program will respond to Excel's formatting, conditional or otherwise.

You might use the code in this http://www.vbaexpress.com/forum/showthread.php?t=36910 to convert the Conditional Formats into normal formats and send that. (That routine has to be altered for post Excel2003)