XLA routines: EE_FormatCols

Nick's picture
EE_FormatCols is a handy routine that takes a source range that is formatted, and applies the formatting to a target range
Sub EE_FormatCols(rngSource As range, rngTarget As range)
    Dim rngTgtHdr   As range
    Dim rngHd       As range
    Dim rngFound    As range
    Dim rngData     As range
    Dim dblDataRows As Double
 
'http://excelexperts.com/xla-routines-eeFormatCols    for updates on this sub routine

    Set rngData = Intersect(rngTarget.CurrentRegion, rngTarget.CurrentRegion.Offset(1))
    Set rngTgtHdr = rngTarget.Rows(1)
 
    dblDataRows = rngData.Rows.Count
 
    For Each rngHd In rngSource.Rows
        Set rngFound = rngTgtHdr.Find(what:=rngHd.Cells(1).value, LookIn:=xlValues, lookat:=xlWhole)
        If Not rngFound Is Nothing Then
            rngHd.Cells(1).Copy
            rngFound.PasteSpecial xlPasteFormats
            rngHd.Cells(, 2).Copy
            rngFound.Offset(1).Resize(dblDataRows).PasteSpecial xlPasteFormats
            Application.CutCopyMode = False
            Set rngFound = Nothing
        End If
    Next rngHd
 
    Set rngTgtHdr = Nothing
    Set rngHd = Nothing
    Set rngFound = Nothing
    Set rngData = Nothing
End Sub