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 nnn 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

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.