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

Posted on 2012-08-13
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
    LVL 10

    Expert Comment

    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

    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

    Expert Comment


    >> 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

    Author Comment

    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

    Expert Comment

    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

    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

    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

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

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now