CODEWITHSUNDEEP
excelexcel-formulavba
Hightower
Hightower

Reputation: 1008

Copy a Column Format based on header row Excel

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

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 )

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

Upvotes: 2

Views: 1485

Answers (2)

Doc Brown
Doc Brown

Reputation: 20044

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.

Upvotes: 2

James L.
James L.

Reputation: 9451

Setting the format for a whole column will result in a smaller filesize. It seems like a better approach to set the format for a whole column, and then override the format for row #2 with a conditional formatting rule. This way you can avoid VBA altogether, and your formatting rules apply to whole columns or the whole sheet, which is optimal.

Select the entire sheet and create a conditional formatting rule that uses the formula =row()=2 and set the fill to black.

Then you can select an entire column and apply any formatting the whole column that you want. The conditional formula is applied last so the black row #2 remains.

If you only want the background to be set for the whole column, then just set that, and change the font color for just the cells on row #1...

In the end it is the same amount of work, because you have to set the formatting for cells and run the macro, or set the settings for the column and change the font color of the top cell...

FYI - If you only want to make the black bar on row #2 extend the width of the used columns (instead of the width of the entire sheet), then select cell A1 before selecting the whole sheet and use the formula =and(row()=2,a$1<>"") for the conditional formula.

Upvotes: 2

Related Questions