PasteSpecial with Conditional Formats

pergo

New Member
Joined
Apr 2, 2011
Messages
7
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!
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Wouldn't copying as a picture be more suitable in that case?
 

pergo

New Member
Joined
Apr 2, 2011
Messages
7
Is that something possible? That would definitely work.

Could you direct me to how to code that?

Thanks! :D
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
It'd be like:
Code:
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

New Member
Joined
Apr 2, 2011
Messages
7

ADVERTISEMENT

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

pergo

New Member
Joined
Apr 2, 2011
Messages
7
I just tested this and it didn't work. It doesn't seem to copy the range. And it pastes nothing. Any advice?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
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)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,630
Messages
5,523,989
Members
409,553
Latest member
alscno

This Week's Hot Topics

Top