I am making a spread sheet and want to print my header and footer in red. i
have used the help option, but couldn't find any thing I could use there. I
am using the 2003 version of Microsoft Excel.

I have an older version, you have to do a custom header/footer and the font
option (an A) is there.

"mgrenier" wrote:

I am making a spread sheet and want to print my header and footer in red. i
have used the help option, but couldn't find any thing I could use there. I
am using the 2003 version of Microsoft Excel.

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.


--
Please remember to indicate when the post is answered so others can benefit
from it later.


"mgrenier" wrote:

I am making a spread sheet and want to print my header and footer in red. i
have used the help option, but couldn't find any thing I could use there. I
am using the 2003 version of Microsoft Excel.

Yes, you are correct, the font formatting I was referring to does not have
color option.

"KC Rippstein" wrote:

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.


--
Please remember to indicate when the post is answered so others can benefit
from it later.


"mgrenier" wrote:

I am making a spread sheet and want to print my header and footer in red. i
have used the help option, but couldn't find any thing I could use there. I
am using the 2003 version of Microsoft Excel.