Excel cannot do this. Your options would be as follows:
1) Create a graphic which is a red box the size you need to fit across the
page, then insert that graphic in the header and/or footer.
2) Use your first couple of rows as your "header", apply background color,
and instruct it to repeat printing those rows on all pages...however, there's
no good way to add a footer this roundabout way.
You could use a macro to accomplish the second option, but it will change
your data by manually inserting footers every so many rows.
Sub FakeHeaderFooter()
Dim LHeader As String
Dim CHeader As String
Dim LFooter As String
Dim CFooter As String
Dim CBottom As Integer
Dim CRow As Integer
Dim PageSize As Integer
LHeader = "Top Left"
CHeader = "Top Center"
LFooter = "Bottom Left"
CFooter = "Bottom Center"
PageSize = 46
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.PrintArea = ""
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(1)
.RightMargin = Application.InchesToPoints(1)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.Orientation = xlPortrait
End With
CBottom = Range("A16000").End(xlUp).Row
CRow = 1
Do Until CRow > CBottom
If CRow Mod PageSize = 1 Then
Rows(CRow).Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
CBottom = CBottom + 2
Cells(CRow, 1).Value = LHeader
Cells(CRow, 4).Value = CHeader
Range(Cells(CRow, 1), _
Cells(CRow, 8)).Interior.ColorIndex = 34
Range(Cells(CRow + 1, 1), _
Cells(CRow + 1, 8)).Interior.ColorIndex = xlNone
CRow = CRow + 2
ElseIf CRow Mod PageSize = PageSize - 1 Then
Rows(CRow).Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
CBottom = CBottom + 2
Cells(CRow + 1, 1).Value = LFooter
Cells(CRow + 1, 4).Value = CFooter
Range(Cells(CRow + 1, 1), _
Cells(CRow + 1, 8)).Interior.ColorIndex = 34
CRow = CRow + 2
Else
CRow = CRow + 1
End If
Loop
LastPageNumber = PageNumber + 1
LastRow = LastPageNumber * PageSize
If CBottom <> LastRow Then
Range(Cells(LastRow, 1), _
Cells(LastRow, 8)).Interior.ColorIndex = 34
Cells(LastRow, 1).Value = LFooter
Cells(LastRow, 4).Value = CFooter
End If
CBottom = Range("A16000").End(xlUp).Row
CRow = 2
Do Until CRow > CBottom
If CRow Mod PageSize = 1 Then
Cells(CRow, 1).PageBreak = xlManual
End If
CRow = CRow + 1
Loop
End Sub
To change the number of lines per page, just change the value assigned to
the PageSize variable. You can also change what appears in the "header" and
"footer" area by changing what is assigned to the LHeader, CHeader, LFooter,
and CFooter variables.
