Proper syntax for changing font color in excel header/footer

[svdoerga]

I am wondering how you can change the fontcolor in the header or footer in VBA when exporting to excel. I need some text in red. I found the ms page with the codes here. It's saying the formatting is &color and it should be a hexadecimal value. Then on the bottom of the page it says K[color]. I've tried a lot of things like:

&Kff0000
&K[ff0000]
&ff0000
&[ff0000]
&colorff0000
&color[ff0000]

but none are working. Anyobody know the proper syntax?

[NeoPa]

Are you sure this is even possible.

I didn't find any reference to colours when I looked at the manual interface to this in the Font screen.

Tip:
In Excel when curious how to do something in VBA that you can do as an operator :-
Select Tools / Macro / Record New Macro.
From there do the action you are curious about (in this case File / Page Setup... / Custom Header / Font (button)) then see what is created in the VBA editor under VBAProject / Modules / Module1.

I was able to tell that setting the font name and size was :

ActiveSheet.PageSetup.CenterHeader = "&""Arial,Bold""&14ggg"

[svdoerga]

Are you sure this is even possible.

I didn't find any reference to colours when I looked at the manual interface to this in the Font screen.

Tip:
In Excel when curious how to do something in VBA that you can do as an operator :-
Select Tools / Macro / Record New Macro.
From there do the action you are curious about (in this case File / Page Setup... / Custom Header / Font (button)) then see what is created in the VBA editor under VBAProject / Modules / Module1.

I was able to tell that setting the font name and size was :

ActiveSheet.PageSetup.CenterHeader = "&""Arial,Bold""&14ggg"

Thanks for the tip, that will come in handy :)

I found out that it is impossible to change font color in a header in excel 2003. It is possible in excel 2007 though.

A trick to get around the inability to change fontcolor in the header, is to format the first row(s) in the sheet, and assign it (or them) as header from: File->Page setup->Sheet->Print titles->rows to repeat/columns to repeat

[NeoPa]

8-) (or Cool for the uninitiated :->)

The macro recording tip I find invaluable even now after some years coding in Excel VBA.

PS. I don't touch Office 2007 with a bargepole - my stuff is all 2003 & before related.

[svdoerga]

8-) (or Cool for the uninitiated :->)

The macro recording tip I find invaluable even now after some years coding in Excel VBA.

PS. I don't touch Office 2007 with a bargepole - my stuff is all 2003 & before related.

I've been using the macro recorder the last days...it's awesome :) Saves me a lot of time trying to find out how to do stuff on the net! :D

[NeoPa]

Marvellous!

Very pleased to hear it :)

I should add a warning not to rely too heavily on the code it produces mind-you. It's automatically generated and generally quite clumsy.

As a resource to point you at where you should be starting from though, as you say, it's invaluable.

[Sylvain MARBACH]

For Excel 2007, there is indeed a way to change font color in header/footer.

Just use the following syntax : "&K" + color value in hexadecimal ; for example to turn font to red, use "&Kff0000"

Not well documented...

[NeoPa]

I imagine the OP knew about this already Sylvain - hence the inclusion in the first post. Are you suggesting there is some way this can be used that is not evident? Perhaps you could explain if so. As it is, your post simply repeats what was included in the question.

[Sylvain MARBACH]

@NeoPa
I spent a lot of time to tackle this problem. My intend was just to summarize for those who are still searching for a solution to colorize some part of a header/footer:
Excel 2003 and before = not possible
Excel 2007 and after = possible
Among all proposed syntaxes in the first post, only one is working = "&Kff0000" for red... or "&Kc71585" for medium-violet-red and so on

[NeoPa]

Sylvain:
I spent a lot of time to tackle this problem.

I applaud you for that.

It would be a shame then, if your answer were unable to help anyone.

In the first post, Svdoerga says they've tried &kff0000 and it didn't work. You say you have found a way to make it work in Access 2007 and beyond. Svdoerga doesn't say which version they're using (which is unfortunate as all questions should have this information for just such situations as this), but whichever version they're using, either doesn't work with your solution, or can be made to work using your technique. My point was that you didn't say anything about a technique (or any special instructions needed to make this work), so the solution is either not right for the question (even though this is mainly confused by Svdoerga not posting the question properly), or it is right but missing important information.

It would be a shame for you to spend that much time and for no-one to be able to benefit from it no?