Copy a Column Format based on header row Excel

Question

I would like to replicate the format of the top row of an excel sheet to selected cells down each selected column.

I cannot use conditional formatting or any formula in the destination cells, because they contain data and a different conditional formatting formula already.
The Entire Column is grouped. Over time, i may change the grouping level and dont want to re-format the entire sheet... the top row background color has a conditional formatting applied.. using the following formula.
```
Function OutlineLev(inp As Integer) As Integer
OutlineLev = Columns(inp).OutlineLevel
End Function
```

So in the example, cell background on row 1 group level 1 is orange, level 2 is blue and L3 is green. (conditional format rule =OutlineLev(CELL("col",D1))=3 )

You can see that row 2 (yellow cells) have no content. Originally i thought i would hava a formula in that cell, to copy the format of the top row down to the other rows within each column...

Essentially. i am looking for a macro/function/formula that will somehow copy the background of the top row (say Cell D1 in example). down to the rest of the column (Range (D3:D9 in example).

enter image description here

Doc Brown Doc Brown · Accepted Answer · 2012-09-13T19:29:45

If you add this code

Sub ApplyFormatting()
    CopyFormat Range("A3:A10"), Range("A1")
    CopyFormat Range("B3:B10"), Range("B1")
    CopyFormat Range("C3:C10"), Range("C1")
    CopyFormat Range("D3:D10"), Range("D1")
End Sub

Sub CopyFormat(dest As Range, copyfrom As Range)
    dest.Interior.Color = copyfrom.Interior.Color
End Sub

to the code area of your worksheet, you have a simple tool that almost does what you want. The only thing is that you have to call the ApplyFormatting subroutine somewhere. For example, if the colors of the first row depends on values of some cells somewhere on the same worksheet, you could use the Worksheet_Change event of that sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    ApplyFormatting
End Sub

Note that this event is not directly triggered by a change of a background color of some cell, only when a cell value changes. That might proably be enough in your case, but since you forgot to tell us how your first row is formatted exactly, I can only guess.

EDIT: to your comment: there is no need to hardcode the range specification, the above was just an example to demonstrate how to copy formats.

A solution which applies this to the whole sheet will look like this:

Sub ApplyFormatting()
    Dim maxRow As Long, maxCol As Long, col As Long
    maxRow = Cells.SpecialCells(xlCellTypeLastCell).Row
    maxCol = Cells.SpecialCells(xlCellTypeLastCell).Column
    For col = 1 To maxCol
        CopyFormat Range(Cells(3, col), Cells(maxRow, col)), Cells(1, col)
    Next
End Sub

You may have to restrict/adapt this to the rows/columns in your actual sheet, but I hope you get the general idea.

Copy a Column Format based on header row Excel

2 Answers