How to add background color to headers or footers in Excel sheet while printing?
As we know, we can add contents or picture to the header or footer of a sheet, which will display while printing. But have you ever tried to add background color to the headers or footers? Actually, there is no built-in function can handle it, but here I introduce some roundabout ways to achieve the operation.
Add background color to header or footer by Fill color
Add background color to header and footer by VBA
Add background color to header or footer by Fill color
1. Click Page Layout > Page Setup.
2. In the Page Setup dialog, under Margins tab, type 0 into Top, Header, Bottom and Footer textboxes.
3. Click OK. Then select first row and click Home > Merge & Center.
3. Go ahead to select a color from Fill color list to add background color to the merged cells.
4. Repeat step 2 and 3 to add background color to the footer row you want.
Now the page seems to add background color to header and footer.
Add background color to header and footer by VBA
Here is a VBA code can quickly add background color to header and footer at the same time.
1. Press Alt + F11 keys to enable Microsoft Visual Basic for Applications window.
2. Click Insert > Module, copy and paste below code to the new module.
VBA: Add background color to header and footer
Sub FakeHeaderFooter()
'UpdatebyExtendoffice20180628
Dim I, J As Long
Dim xRg As Range
Dim xRow, xCol As Long
Dim xRows, xCols As Long
Dim xDivRow, xDivCol As Long
Dim xTopArr, xButtArr As Variant
Dim PageSize1, PageSize2 As Integer
On Error Resume Next
xTopArr = Array("Top Left", "", "", "Top Center", "", "", "", "")
xButtArr = Array("Bottom Left", "", "", "Bottom Center", "", "", "", "")
PageSize1 = 46
PageSize2 = 8
With ActiveSheet.PageSetup
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.BlackAndWhite = False
End With
Set xRg = ActiveSheet.UsedRange
If xRg Is Nothing Then Exit Sub
xRows = xRg(xRg.Count).Row
xCols = xRg(xRg.Count).Column
If xRows < 46 Then xRows = 46
If xCol < 8 Then xCol = 8
xDivRow = Int(xRows / PageSize1)
xDivCol = Int(xCols / PageSize2)
If ((xRows Mod PageSize1) > 0) And (xDivRow <> 0) Then xDivRow = xDivRow + 1
If ((xCols Mod PageSize2) > 0) And (xDivCol <> 0) Then xDivCol = xDivCol + 1
If xDivRow = 0 Then xDivRow = 1
If xDivCol = 0 Then xDivCol = 1
Set xRg = Range("A1").Resize(xDivRow * PageSize1, xDivCol * PageSize2)
xRow = 1
xCol = 1
Cells.PageBreak = xlPageBreakNone
For I = 1 To xDivRow * PageSize1 Step PageSize1 + 1
For J = 1 To xDivCol * PageSize2 Step PageSize2
Cells(I, J).Resize(1, PageSize2) = xTopArr
Cells(I, J).Resize(1, PageSize2).Interior.ColorIndex = 34
Cells(I + PageSize1, J).Resize(1, PageSize2) = xButtArr
Cells(I + PageSize1, J).Resize(1, PageSize2).Interior.ColorIndex = 34
Rows(I + PageSize1 + 1).PageBreak = xlManual
Columns(J + PageSize2).PageBreak = xlManual
Next
Next
End Sub
3. Press F5 key to run the code, then certain background color has been added to the first row and the 47th row of each sheet which will shown as fake header and footer.
Note: in the code, you can change below scripts to meet your need.
1. Change the header and footer contents as you need in the string:
xTopArr = Array("Top Left", "", "", "Top Center", "", "", "", "")
xButtArr = Array("Bottom Left", "", "", "Bottom Center", "", "", "", "")
2. Change the background color index in this sting:
Cells(I, J).Resize(1, PageSize2).Interior.ColorIndex = 34
Cells(I + PageSize1, J).Resize(1, PageSize2).Interior.ColorIndex = 34
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
