Excel 2007, ThemeColor Light and Dark reversed

[Guest]

In Excel 2007, if I do:

Selection.Interior.ThemeColor = xlThemeColorLight1
Selection.Font.ThemeColor = xlThemeColorDark1

then I'd expect to see dark (black) text on a light (white) background, but
I'm actually getting the opposite - light text on a dark background. It's
also wrong if I try it the other way around and xlThemeColorDark2 and
xlThemeColorLight2 seem to be reversed in the same way, too. (And, no, I
haven't changed the theme colours from default).

Am I missing something completely obvious? I can't work out how this can be
expected behaviour. I'd be grateful if anyone could enlighten me.

Thanks
Chris

 

[Jialiang Ge [MSFT]]

Hello Chris,

From your post, my understanding on this issue is: you want to know why the
Selection's background color turns black and foreground is white when
Selection.Interior.ThemeColor is set to be xlThemeColorLight1 and
Selection.Font.ThemeColor = xlThemeColorDark1, and you wonder how to
reverse the colors. If I'm off base, please feel free to let me know.

According to the MSDN article:
http://msdn2.microsoft.com/en-us/library/Bb216356.aspx, the xlThemeColor
enumeration 'xlThemeColorLight1' is mapped to value 2, and
'xlThemeColorDark1' stands for value 1 by default.

The ThemeColor property uses the color table below, in which '1' means
White and '2' means Black.
'White = 1
'Black = 2
'Gray = 3
'Dark Blue = 4
'Light Blue = 5
'Maroon = 6
'Olive = 7
'Light Purple = 8
'Teal = 9
'Orange = 10
'Navy Blue = 11
'Dark Purple = 12

Therefore, to make the background color White and the foreground color
Black, please try the following VBA codes:
Selection.Interior.ThemeColor = xlThemeColorDark1 ' or 1
Selection.Font.ThemeColor = xlThemeColorLight1 ' or 2

Another approach is to use ColorIndex property instead of ThemeColor. In
ColorIndex's color table (http://support.microsoft.com/kb/192906) , '2' is
for White and '1' for Black.
Selection.Interior.ColorIndex = 2 ' White
Selection.Font.ColorIndex = 1 ' Black

If it still does not work, I wonder whether you have ever changed the color
palette for this workbook? (http://support.microsoft.com/kb/288412). The
change of the default palette will influence the corresponding color of
ColorIndex.

Please let me know if you have any other concerns, or need anything else.

Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Thank you for re-summarizing the issue and confirming that Microsoft have
defined Light=Black and Dark=White.

However, if you inspect
ActiveWorkbook.Theme.ThemeColorScheme.Colors(msoThemeDark1).RGB then you see
it is defined as black as expected. Granted, this method is using a different
enumeration to XlThemeColor, but there does seem to be a one-to-one mapping
with msoThemeDark1 = xlThemeColorDark1 = 1 (similarly for the other
enumeration constants).

My question is not how to reverse the colours in a specific instance - I
somehow managed to work that out on my own.

I would like to know the explanation behind this counter-intuitive
behaviour. If there is a good reason for it then I can continue to write
backwards code with some semblance of confidence. If, however, it is
considered a bug, then I would like to know that when it is fixed it will be
done in such a way as that it won't break any of the code I have written.

Thanks
Chris

 

[Jialiang Ge [MSFT]]

Hello Chris,

Thank you for reporting the issue of Office 2007. I have consulted Office
development team, and they confirmed that it is a known issue of the office
2007 which will be fixed in the next version of Office: Office 14.

A workaround for this bug is to set the property 'Color' instead of
'ThemeColor' because we can get the correct color through
ActiveWorkbook.Theme.ThemeColorScheme.Colors(msoThemeLight1).RGB. Please
refer to the following codes:

Selection.Interior.Color =
ActiveWorkbook.Theme.ThemeColorScheme.Colors(msoThemeLight1).RGB
Selection.Font.Color =
ActiveWorkbook.Theme.ThemeColorScheme.Colors(msoThemeDark1).RGB

In this way, we do not need to worry about the break of the code, even if
the bug is fixed in future.

We, the newsgroup team, have also logged this issue for future reference.
Thank you again for your sharing.

Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jialiang Ge [MSFT]]

Hello Chris,

Thank you for reporting the issue of Office 2007. I have consulted Office
development team, and they confirmed that it is a known issue of office
2007 which will be fixed in the next version of Office.

A workaround for this bug is to set the property 'Color' instead of
'ThemeColor' because we can get the correct color through
ActiveWorkbook.Theme.ThemeColorScheme.Colors(msoThemeLight1).RGB. Please
refer to the following codes:

Selection.Interior.Color =
ActiveWorkbook.Theme.ThemeColorScheme.Colors(msoThemeLight1).RGB
Selection.Font.Color =
ActiveWorkbook.Theme.ThemeColorScheme.Colors(msoThemeDark1).RGB

In this way, we do not need to worry about the break of the code, even if
the bug is fixed in future.

We, the newsgroup team, have also logged this issue for future reference.
Thank you again for your feedback on our products and help us import them.
We have submitted request to document this issue as a KB article which
could benefit other developers in the future.

Please feel free to let me know if you have any concern or need anything
else.

Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jialiang Ge [MSFT]]

Hello Chris,

Thank you for reporting the issue of Office 2007. I have consulted Office
development team, and they confirmed that it is a known issue of office
2007 which will be fixed in the next version of Office.

A workaround for this bug is to set the property 'Color' instead of
'ThemeColor' because we can get the correct color through
ActiveWorkbook.Theme.ThemeColorScheme.Colors(msoThemeLight1).RGB. Please
refer to the following codes:

Selection.Interior.Color =
ActiveWorkbook.Theme.ThemeColorScheme.Colors(msoThemeLight1).RGB
Selection.Font.Color =
ActiveWorkbook.Theme.ThemeColorScheme.Colors(msoThemeDark1).RGB

In this way, we do not need to worry about the break of the code, even if
the bug is fixed in future.

We, the newsgroup team, have also logged this issue for future reference.
Thank you again for your feedback on our products and helping us improve
them.
We have submitted a request to document this issue as a KB article which
could benefit other developers in the future.

Please feel free to let me know if you have any concern or need anything
else.

Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Thanks for confirming the bug.
Just to point out (to anyone else reading this) that the suggestion of
setting colours using the RGB values is not a full workaround, since those
colours will not subsequently change when the theme or colour scheme is
changed (which is the point of using theme colours in the first place).

 

[Walter Wang [MSFT]]

Hi Chris,

This is Walter, community lead of the MSDN Managed Newsgroup Support Team.

Thank you so much for your feedback and sharing the workaround for this
issue. I believe that other community members will also benefit from your
experience sharing. Without the input of numerous intelligent and
warmhearted customers like you, our MSDN Managed Newsgroup can never be as
comprehensive and helpful as it is today. I appreciate your time and
efforts working with us.

I am contacting you to see whether there is anything we can do for you. If
you have any comments on our support service, website design, our product
limitation, our process, and etc., please don't hesitate to let me know. I
will do my best to follow up.

Thank you for using our MSDN Managed Newsgroup Support Service.


Regards,
Walter Wang ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[GillyUK]

Hi - I know this is a bit on now, but there still seems to be an issue with the colour palettes in Office 365. Not so much in the code as above (I haven't tested), but compatibility between Excel/Word and PowerPoint. In PowerPoint the colour palette displays as per the design window, with 'Text/Background - Dark 1' and 'Text/Background - Light 1' the first and second colours in the palette respectively. However, in Excel and Word the colour palette has these reversed. So, if your theme has Black in Dark 1 and White in Light 1, and you colour your text black in a PowerPoint chart and then copy this to Excel, all the text in the chart goes white (and vice versa). It makes using themes between Excel and PowerPoint almost impossible to implement effectively.

Is there a work around for this at all, short of assigning a specific 'black' and 'white' colour that isn't in the pallet to text and backgrounds... which seems counter intuitive to the use of themes...

 

[Sleepy319]

Hi,

14 years on and this is still not fixed. xlThemeColorLight2 and xlThemeColorDark2 are still reversed.

Surely that's super simple to resolve - is there a particular reason why it's still the way it is?