Header Footer Format and formula

Question

I would like to take a cell reference to another worksheet and put that in the center header and format it to a specific font and size. Next, I'd like to take the Right header and have either the name of my report as the top, with a bold font of specific size while the line below has a separate size and is not bold. Like below:

Compliance Report (This doesn't need to be cell referenced, it can stay the same).

Policy Date 2014 | 9/30/15 (This changes from account to account and need to come from a source range.)

I started with this code:

Sub header()
    With Worksheets("Sheet1").PageSetup
        .RightHeader = Worksheets("Compliance Report").Range("a99") _
            & Chr(10) & Worksheets("Compliance Report").Range("a100")
        End With
End Sub

It works for getting the data on two lines, but I can't format it. I'm not tied to sticking with this formula if there is a better option.

Answer 1

Please try:

Sub RHeader()
    With Worksheets("Sheet1").PageSetup
       .RightHeader = "&""Courier New,Bold""&12&KFF0000" _
       & Worksheets("Compliance Report").Range("a99") & Chr(10) _
       & "&""Courier New,Regular""&10&K000000" _
       & Worksheets("Compliance Report").Range("a100")
    End With
End Sub  

10 and 12 are the font sizes, KFF0000 is red, hopefully the rest self-explanatory and adjustable.