Retain Conditional Format Color When Copying A Range To Another Sheet Or Workbook

[Excel Fox]

A lot of the inspiration for this code originated from Rick's Get Displayed Cell Color Whether From Conditional Formatting Or Not (http://www.excelfox.com/forum/f22/get-displayed-cell-color-whether-from-conditional-formatting-or-not-338/)


Mine is probably not the most intuitive of codes, but it does very good when the user just wants to retain the display format (conditional format color) of the range being copied from one worksheet to another worksheet in the same workbook, or even another workbook. For extremely large data range, this could turn out to be a bit slow, however, effective all the same.


Sub CopyRangeWithConditionalFormat()

RetainConditionalFormatWhenCopyingRange Worksheets("Sheet1").Range("A1:K20"), Worksheets("Sheet2").Range("D10")

End Sub

Sub RetainConditionalFormatWhenCopyingRange(rngSource As Range, rngDestination As Range)

Dim varSourceCF As Variant, varSourceNF As Variant
Dim lngCol As Long, lngRow As Long
ReDim varSourceCF(1 To rngSource.Rows.Count, 1 To rngSource.Columns.Count)
ReDim varSourceNF(1 To rngSource.Rows.Count, 1 To rngSource.Columns.Count)
For lngCol = 1 To rngSource.Columns.Count
For lngRow = 1 To rngSource.Rows.Count
varSourceCF(lngRow, lngCol) = rngSource(lngRow, lngCol).DisplayFormat.Interior.Color
varSourceNF(lngRow, lngCol) = rngSource(lngRow, lngCol).NumberFormat
Next
Next
rngDestination.Resize(lngRow - 1, lngCol - 1).Value = rngSource.Value2
For lngCol = 1 To rngSource.Columns.Count
For lngRow = 1 To rngSource.Rows.Count
rngDestination(lngRow, lngCol).Interior.Color = varSourceCF(lngRow, lngCol)
rngDestination(lngRow, lngCol).NumberFormat = varSourceNF(lngRow, lngCol)
Next
Next

End Sub