Excel 2010 - Themes not being applied to one of my worksheets.

I have a two worksheets inside of a workbook and would like to apply a theme to both of the worksheets.  However, one of the sheets is not accepting the theme.  How can I fix this?  (See attached workbook)
Who is Participating?
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
You will need to exchange all the applied non-theme colors with theme colors.

Office 2007 and later offers themes and presents themes in the color palette in a prominent position. The theme colors in the color picker list two background (light) and two text (dark) colors, followed by six colors for accents 1 through 6. All these also come with different shades of intensities.

Internally, Office will store theme-colored elements as having the color Accent1-20% for example.  In Theme A, this Accent1 may be red. In Theme B, this Accent1 may be green. If the theme is changed, the color will change, since its reference to a color is "relative" to the chosen theme.

If the user ignores these theme colors and selects from the standard colors instead, or a color from the color wheel in the "more colors" dialog, then the element will be formatted with the absolute RGB value of the chosen color.

This element is then not linked to a theme at all. The only way to link the element back to the theme is to apply a theme color.

cheers, teylyn
You have to use Theme-related fonts/fills/borders/etc.  If you use "Standard" elements, it won't change when you change Themes.

See last post on this thread:  Issue with Themes

As a quick fix, select the upper left square on the sheet that "works" to select the entire sheet, right-click/copy, then select all on the sheet that doesn't work, right-click/paste special/format.

Now, if you change the theme, it will change on both sheets.
brothertruffle880Author Commented:
I didn't use ANY fonts, fills borders.  As you can see on the sheet, I merely did a
HomeTab/ Editing group, CLEAR button /Clear FORMATS
to reset that worksheet back to its bare minimum.  i.e. basic font, color, sizes, etc.  
I am utterly stumped.
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

>> I merely did a [...] clear Formats

Exactly therein lies the problem. The cells now don't have any formatting, so theme colors won't be applied and neither will theme changes reflect.

Copy A1 to F12 on the second tab, select A1 on the first tab and use Paste Special > Formats

Now the formats are back and the colors will change if you select a different theme.

cheers, teylyn
brothertruffle880Author Commented:
I thought when you did a clear formats, you were resetting things back to a unmolested state.  

Based on what I'm reading from the two of you, clear formats is like using a "standard" color.  i.e. themes will have NO effect on the cells you use the standard colors on or on the cells you do a clear formats on.

Is there a VBA term here that might serve to clarify?
1.  What is the name of the cell state where you can apply a theme to a cell and the theme gets applied.
2.  What is the name of the cell state that I created by clearing my formatting, or by applying a standard color?  I thought clearing formatting was "resetting" the cell back to a pristine state where themes would take affect.  But apparently I molested the cells in some invisible way!  It seems that clearing formatting is just as invasive as setting a cell to a standard color.

I'm trying to understand the logic of cell states and their restoration to a state where themes can work
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Trying to clear up some confusion with themes:

- you can see the theme colors in the color dropdown for font color or cell fill.
- if you select from one of these visible colors, you are applying a theme color.
- if you select from one of the Standard Colors or click "More colors" you are NOT applying a theme color.
- Elements formatted with theme colors will change when you change the theme
- Elements formatted with non-theme colors, i.e. "Standard colors" or via "more colors" will NOT change when you change the theme

And finally: Clearing all formats will remove any formatting from the selected elements and you end up with the default font for Excel.

cheers, teylyn
brothertruffle880Author Commented:
Question:  I have a worksheet with all sorts of odd changes on it which are not allowing themes to take effect.  How can I take this worksheet and restore it to a state where all themes applied will take effect.
brothertruffle880Author Commented:
Jeepers.  I didn't think there was so much to know about themes.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.