Change font and/or font size in headers, footers and body via VBA

[BEEJAY]

Greetings:

Having major formatting problems when trying to run my Windows based W/B's
on a Mac.
It appears that the only way I can resolve the problems is to change the
font sizes (and likely also the column width settings) on a per SHEET basis.
To do this effectively, I'm hoping there is a way that code could be used to
"read" existing font size, then decrease it by ONE point.
Once actual font size required is determined, the code would be adjusted to
do its required changes in one run thru.
Require (adjustable) changes to: Header, Footer and (active) Body of
worksheet.

In order to (hopefully) help expedite this problem, I'm changing all my
windows based masters to use only the Arial font group.

Any assistance in this matter would be greatly appreciated.

 

[Paul C]

The body is fairly simple

ftsize=Activecell.font.size will give you the font size of a given cell

A loop like

For Each Sh In Workbooks(TgtWorkbookName).Worksheets
For Each Cell In Sh.UsedRange.Cells
Cell.font.size=cell.font.size-1
Next Cell
Next Sh

Will reduce the font size in every cell by 1

The headers and footers are another story
I am not sure if you can directly set the size

ActiveSheet.CenterHeader.Font.Size = 16
and ActiveSheet.PageSetup.CenterHeader.Font.Size = 16

Both error out.

The font size appears to be buried in the header
aa=Activesheet.PageSetup.CenterHeader
Yields something like "&16Page &P" with the &16 experession representing the
font size.

To change all of these you would have to pull in the Right, Center and Left
Headers and Footers,
use some thing to isolate the 16 (to make this even more difficult it could
be a single digit like &8), subtract 1 from it and then replace the 16 with
the resulting 15 in the string and send it back to the sheet

ActiveSheet.PageSetup.CenterHeader="newstring"

if they are all the same size it is a little easier, in that you could pull
one sample and not repeat the subtraction. But you still have to replace
every section.

Sounds like a major pain, hopefully someone else has a better idea.