Copy and Paste Value - With Coloring

Question

I am in the process of developing an Export macro on various worksheets in a workbook. That being said, i need to have the worksheets with the Export macro to export the values of a specified range (named range) and the color formats they hold from a conditional format.

One thing I do not need is to copy the conditional formats that created the coloring. I only want the resulting color of the various cells in the range.

I have done this, code below, but when i open the rollup file, all the cells in question have the conditional formats pattern associated with them, which results in a coloring problem.

ws.range("rngAreaMetricDetail").Copy   'Area Mgr Store Metrics
newws.range("V3").PasteSpecial xlPasteValues    'Paste Values
newws.range("V3").PasteSpecial xlPasteFormats  'Paste Coloring
newws.Names.Add "rngAreaMetricDetail", Selection   'Create Named-Range from Selection

Thanx in advance.

Answer 1

Excel doesn't have an easy way to convert a conditional format into the results of the conditional format. You have to do everything manually:

• Check to see if the FormatCondition is being used on each cell.
• Manually assign the formats from the FormatCondition. (Borders, Font, Interior, & NumberFormat)
• If you have more than one FormatCondition, the latter formats override the earlier ones unless StopIfTrue is set.

If you have Microsoft Word installed you can copy your range to Word and back to Excel letting Word take care of converting the formats.

Sub CopyConditionalFormattingThruWord(sAddress As String)
   Dim appWord As Word.Application, doc As Word.Document
   Dim wbkTo As Workbook

   ' copy from original table
   ThisWorkbook.Activate
   ThisWorkbook.Names!rngAreaMetricDetail.RefersToRange.Copy

   ' paste into word application and recopy
   Set appWord = New Word.Application
   With appWord
      .Documents.Add DocumentType:=wdNewBlankDocument
'      .Visible = True
      .Selection.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
      .Selection.HomeKey Unit:=wdStory, Extend:=wdExtend
      DoEvents
      .Selection.Copy
   End With

   ' copy to new workbook
   Set wbkTo = Excel.Application.Workbooks.Add
   wbkTo.Worksheets(1).Range(sAddress).Select
   wbkTo.ActiveSheet.Paste
   DoEvents

   ' close Word
   appWord.Quit SaveChanges:=False

   MsgBox "Done."

End Sub

Note: This doesn't copy the formatting 100% correctly but for most things, it is probably good enough. In the below example, I have 3 conditional formats applied to rows 1-9 in the table on the left. The table on the right is the result of running CopyConditionalFormattingThruWord sAddress:="B3".

Excel 2010: If you were using Excel 2010, and didn't want to use Word, you can skip the FormatCondition testing by using the range's new DisplayFormat member. From the help file:

Actions such as changing the conditional formatting or table style of a range can cause what is displayed in the current user interface to be inconsistent with the values in the corresponding properties of the Range object. Use the properties of the DisplayFormat object to return the values as they are displayed in the current user interface.

You still have to manually assign the values from its Borders, Font, Interior, & NumberFormat etc.