Header code

[Bdavis]

I am using the following macro to creat header/footers:

Sub Footer()
'
' Footer Macro
With ActiveSheet.PageSetup
.LeftHeader = ActiveSheet.Range("b2").Text & _
ActiveSheet.Range("b3").Text & _
ActiveSheet.Range("b4").Text
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&""Times New
Roman,Bold""Confidential Draft"
.CenterFooter = "&""Times New Roman,Bold""&P of &N"
.RightFooter = ""
End With
End Sub


As you can see, the left header is created by combining
three cells on the active sheet. What I'd like to do (and
can't figure out) is to put carrage returns between those
three lines and pull the formatting of the referenced
cells inot the header. I thought the above code would do
that but it doesn't seem to work... the header remains
unformatted.

Thanks!

 

[Ron de Bruin]

Hi Bdavis

With ActiveSheet.PageSetup
.LeftHeader = ""
.LeftHeader = ActiveSheet.Range("b2").Text & Chr(10) & _
ActiveSheet.Range("b3").Text & Chr(10) & _
ActiveSheet.Range("b4").Text
End With

More info you can find here
http://www.cpearson.com/excel/headfoot.htm

 

[Bdavis]

Thanks Ron... but this still is not pulling in the format
of the source cell. For example. Cell B2 is Bold but the
header related to that cell is not.

 

[Ron de Bruin]

Try this to make the first cell bold

Sub aa()
With ActiveSheet.PageSetup
.LeftHeader = ""
.LeftHeader = "&B" & ActiveSheet.Range("b2").Text & Chr(10) & _
"&B" & ActiveSheet.Range("b3").Text & Chr(10) & _
ActiveSheet.Range("b4").Text
End With
End Sub

 

[Guest]

Hey Ron,

I'm sorry, I'm not being clear. I don't want to add the
formatting to the code, I wan the code to oull the
formatting from the source cell.

-----Original Message-----
Try this to make the first cell bold

Sub aa()
With ActiveSheet.PageSetup
.LeftHeader = ""
.LeftHeader = "&B" & ActiveSheet.Range("b2").Text & Chr(10) & _
"&B" & ActiveSheet.Range ("b3").Text & Chr(10) & _
ActiveSheet.Range("b4").Text
End With
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bdavis" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Hi

I think that this is not possible with a few lines of code.
Maybe anybody else have experience with this.

 

[NickHK]

Bdavis,
I guess you would have to convert the cell's formatting into the form that
the header/footer understands. Something like:
(air-code)
Sub Macro1()
With ActiveSheet

.PageSetup.LeftFooter = "&""" & FooterFormat(.Range("A1")) & """&S" &
..Range("A1").Value & """" & Chr(10) & _
"&""" & FooterFormat(.Range("A2")) & """&S" &
..Range("A2").Value & """"

End With
End Sub

Private Function FooterFormat(argInputRange As Range) As String
Dim TempStr As String

With argInputRange.Font
TempStr = .Name
If .Bold = True Then
TempStr = TempStr & ",Bold"
End If
'.
'+other formats
'.
FooterFormat = TempStr
End With
End Function

NickHK