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

Posted on 2012-08-13
Medium Priority
Last Modified: 2012-08-15
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)
Question by:brothertruffle880
  • 4
  • 3
LVL 10

Expert Comment

ID: 38289747
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.

Author Comment

ID: 38289809
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.
LVL 50
ID: 38290015

>> 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 38290464
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
LVL 50
ID: 38290527
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

Author Comment

ID: 38291252
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.
LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 38291390
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

Author Closing Comment

ID: 38298712
Jeepers.  I didn't think there was so much to know about themes.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question