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

A lot of the inspiration for this code originated from Rick's Get Displayed Cell Color Whether From Conditional Formatting Or Not


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.

Code:

---

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

---