A "text effect" in Headers or Footers

I tried to customize headers in my workbook and I discovered a bug. I suppose I'm not the first one and I know I'm not the last one. This bug isn't serious, moreover could be a little bit helpful. I like it and it's quite interesting, in my opinion. The point is that you can overlay the same text several times by using a simple trick. But you can't do it manually. To make it possible you must use the VBA.
You can customize headers or footers in three sections: LeftHeader, CenterHeader or RightHeader. In the VBA you have to use these formatting codes. The first three (&L, &C, &R) are very interesting. What will happen if you use one of them in a different section, &R in LeftHeader for example? Run this code:

Select all

ActiveSheet.PageSetup.LeftHeader = "&R&20My header text 1."
ActiveSheet.PrintPreview

I would expect that the text will be displayed in LeftHeader and aligned to the right side. But it's not true. It is displayed in RightHeader. Now change the text to "My header text 2" (to better demonstrate what will happen) and run this code once again. The original text won't be rewritten, but overlayed with the new one. However, it doesn't happen in case you use the same formatting code in the same section (e.g. &R in the RightHeader) or you omit the alignment formatting code at all. Moreover, this can be used to clear a section.

Select all

ActiveSheet.PageSetup.RightHeader = vbNullString
ActiveSheet.PageSetup.LeftHeader = "&R&20My header text 1."
ActiveSheet.PageSetup.LeftHeader = "&R&20My header text 2."
ActiveSheet.PrintPreview

I admit there aren't many things which you can do with it. Surely you get a better result by inserting an image. But you can't insert for example page numbers into an image.

A few examples

Select all

Sub subHeaderEffect1()
  Const csFONT As String = "&""Calibri"""
  Const csSIZE As String = "&10"
  Const csCOLOR1 As String = "&KFF0000"
  Const csCOLOR2 As String = "&K000000"
  
  With ActiveSheet.PageSetup
  .LeftHeader = vbNullString
  .CenterHeader = vbNullString
  .RightHeader = vbNullString
  
  Dim sText As String
  sText = "Page &P of &N" & vbCr & "&D &T"
  
  .LeftHeader = "&R&U" & csFONT & csSIZE & csCOLOR1 & sText
  .LeftHeader = "&R" & csFONT & csSIZE & csCOLOR2 & sText
  End With 'ActiveSheet.PageSetup
  ActiveSheet.PrintPreview
End Sub

Select all

Sub subHeaderEffect2()
  Const csFONT As String = "&""Times New Roman"""
  Const csSIZE As String = "&10"
  Const csCOLOR1 As String = "&K2525FF"
  Const csCOLOR2 As String = "&KAAAAAA"
  
  With ActiveSheet.PageSetup
  .LeftHeader = vbNullString
  .CenterHeader = vbNullString
  .RightHeader = vbNullString
  
  Dim sText As String
  sText = csCOLOR1 & "&IPage &P of &N&I" & vbCr & "&E&BMy very interesting book&B"
  .LeftHeader = "&R" & csFONT & csSIZE & sText
  
  sText = csCOLOR2 & "&IPage &P of &N&I" & vbCr & "&BMy very interesting book&B"
  .LeftHeader = "&R" & csFONT & csSIZE & sText
  
  sText = csCOLOR2 & "&IPage &P of &N&I" & vbCr & "&B&K000000My very interesting book&B"
  .LeftHeader = "&R" & csFONT & csSIZE & sText
  End With 'ActiveSheet.PageSetup
  ActiveSheet.PrintPreview
End Sub

Select all

Sub subHeaderEffect3()
  Const csFONT As String = "&""Arial Black"""
  Const csSIZE As String = "&12"
  
  With ActiveSheet.PageSetup
  .LeftHeader = vbNullString
  .CenterHeader = vbNullString
  .RightHeader = vbNullString
  .LeftHeader = "&R" & csFONT & csSIZE & "&KB7B7B7Page &P &KB7B7B6of &N"
  .LeftHeader = "&R" & csFONT & csSIZE & "&K257534Page &P of &N"
  End With 'ActiveSheet.PageSetup
  ActiveSheet.PrintPreview
End Sub

Select all

Sub subHeaderEffect4()
  Const csFONT As String = "&""Consolas"""
  Const csSIZE As String = "&20"
  Const csCOLOR1 As String = "&K111111"
  Const csCOLOR2 As String = "&KFFFFFF"
  Const csTEXT As String = "COOOOOOOL ;)"
  
  With ActiveSheet.PageSetup
  .LeftHeader = vbNullString
  .CenterHeader = vbNullString
  .RightHeader = vbNullString
  .LeftHeader = "&R&B" & csFONT & csSIZE & csCOLOR1 & csTEXT
  .LeftHeader = "&R" & csFONT & csSIZE & csCOLOR2 & csTEXT
  End With 'ActiveSheet.PageSetup
  ActiveSheet.PrintPreview
End Sub

Tweet