Formatting and VBA Codes for Headers and Footers

The following special formatting and Visual Basic for Applications (VBA) codes can be included as a part of the header and footer properties (LeftHeader, CenterHeader, RightHeader, LeftFooter, CenterFooter, and RightFooter).

Format code Description
&;L Left aligns the characters that follow.
&;C Centers the characters that follow.
&;R Right aligns the characters that follow.
&;E Turns double-underline printing on or off.
&;X Turns superscript printing on or off.
&;Y Turns subscript printing on or off.
&;B Turns bold printing on or off.
&;I Turns italic printing on or off.
&;U Turns underline printing on or off.
&;S Turns strikethrough printing on or off.
&;"fontname" Prints the characters that follow in the specified font. Be sure to include the double quotation marks.
&;nn Prints the characters that follow in the specified font size. Use a two-digit number to specify a size in points.
&;color Prints the characters in the specified color. User supplies a hexadecimal color value.
&;"+" Prints the characters that follow in the Heading font of the current theme. Be sure to include the double quotation marks.
&;"-" Prints the characters that follow in the Body font of the current theme. Be sure to include the double quotation marks.
&;K xx. S yyy Prints the characters that follow in the specified color from the current theme. xx is a two-digit number from 1 to 12 that specifies the theme color to use. S nnn specifies the shade (tint) of that theme color. Specify S as + to produce a lighter shade; specify S as - to produce a darker shade. nnn is a three-digit whole number that specifies a percentage from 0 to 100.
If the values that specify the theme color or shade are not within the described limits, Excel will use the nearest valid value.
VBA code Description
&;D Prints the current date.
&;T Prints the current time.
&;F Prints the name of the document.
&;A Prints the name of the workbook tab.
&;P Prints the page number.
&;P+number Prints the page number plus the specified number.
&;P-number Prints the page number minus the specified number.
&;&; Prints a single ampersand.
&;N Prints the total number of pages in the document.
&;Z Prints the file path.
&;G Inserts an image.

Example

The following code shows how formatting and VBA codes can be used to modify the header information and appearance.

Sub Date_Time() 
 ActiveSheet.PageSetup.CenterHeader = "&;D &;B&;ITime:&;I&;B&;T" 
End Sub