New Line in headers with VBA

[smit3446]

I'm trying to have my header say:

Apples
Oranges
Bananas

But my code is giving an output of...

Apples

Oranges

Bananas

...with an extra line break in there. How do I fix this?

Here is my current code:

Sub header_footer_unscale()Dim ws As Worksheet




For Each ws In ActiveWorkbook.Worksheets
   
    With ws.PageSetup
        .ScaleWithDocHeaderFooter = False
        .TopMargin = Application.InchesToPoints(0.9)
        .RightHeader = "Apples" & vbNewLine & "Oranges" & vbNewLine & "Bananas"
        
    End With
    ActiveWindow.View = xlNormalView
    Range("A1").Activate
    
Next
            
End Sub

Thanks!

 

[Roderick_E]

maybe replace vbNewLine with vbcr?

 

[smit3446]

Thank you, this worked. New question - I want my text in my header to be Bold, size 8, arial narrow, and a certain kind of purple that my company uses for its branding. I can't figure out the syntax for this code. This is what I had...

Sub header_footer_unscale()Dim ws As Worksheet




For Each ws In ActiveWorkbook.Worksheets
   
    With ws.PageSetup
        .ScaleWithDocHeaderFooter = False
        .TopMargin = Application.InchesToPoints(0.9)
        .RightHeader =&B &"Arial Narrow" &08 "Apples" & vbNewLine & "Oranges" & vbNewLine & "Bananas"
        
    End With
    ActiveWindow.View = xlNormalView
    Range("A1").Activate
    
Next
            
End Sub

I have no idea how to figure out the "hexadecimal" of the required purple I need. The RGB color for the purple is:

R: 112
G: 32
B: 130