In the UI, you have some formatting options when entering a custom footer (File > Page Setup > Header/Footer). You also have those options when creating a footer in VBA, you just have to know the codes to use.
Special codes like page numbers and dates can be found in the constant definition section of the code below. It’s an ampersand followed by a letter (A for the sheet name, who came up with that?).
For fonts and font sizes, the format is
ampersand + font name in double quotes + ampersand + font size
which is all optional, that is, you only need to include the name or the size if you want to change them. I know exactly two font names from memory, so if you’re like me the easiest way to set this up is to do it in the UI and check the LeftFooter, CenterFooter, and/or RightFooter properties of the PageSetup object. Of course, all this applies to headers as well.
Here’s an example that creates a formatted footer and uses some (all?) of the special codes.
Dim sLeft As String
Dim sCenter As String
Dim sRight As String
Const sPAGE As String = “&P”
Const sPAGES As String = “&N”
Const sFILE As String = “&F”
Const sSHEET As String = “&A”
Const sDATE As String = “&D”
Const sTIME As String = “&T”
sLeft = sPAGE & ” of “ & sPAGES
sCenter = “&”“Albertus Medium,Bold”“&11This&”“Arial,Regular”“&10 is formatted text”
sRight = “[“ & sFILE & “]” & sSHEET & Chr$(10) & sDATE & ” “ & sTIME
.LeftFooter = sLeft
.CenterFooter = sCenter
.RightFooter = sRight