Add custom colors in Excel 2010





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







5















I'm working on a spreadsheet which was created in an earlier version of Excel (2003 I think), and it makes extensive use of color-coding for organization. Unfortunately, it was built using the color-scheme from the older version, and basically none of the colors match those in the 2010 theme (except for pure white and pure black). So every time I want to make something conform to the previously existing color-scheme, I either have to choose a custom color and select it by hand, or I have to copy formatting from an existing cell and paste into the target cell (which is annoying because it also copies stuff like borders, etc.)



I've found the Page Layout/Themes/Colors setting, but it seems all I can do there is change the selectable colors to one of their presets, or create a completely new palette - and I only get to select a few colors in that palette, not the full set. It creates the rest by making lighter or darker versions of the colors you choose. Plus, doing either of these things will change the colors already used on the spreadsheet if and only if they exactly match colors used in the previous scheme. This means that things which used to be colored in similar tones to denote their relationship are now colored differently, some remaining in the old scheme and others being automatically changed to the new. It basically breaks the whole system.



What I want is to be able to just add the specific colors I use from the old document so that they appear in the color chooser and I don't have to go searching for them. Older versions of Excel had this capability - there were a bunch of customizable squares that you could make any color you want and they'd be saved with the theme. I think you could even add more squares, I forget. (Don't have the old version installed anymore.) Did they remove this feature in favor of their "Themes"? Or is it hidden away somewhere? I'm not at all interested in this color schemes feature. I just want to use whatever colors I want to use (particularly the ones that match this document) without having to worry about whether they match some arbitrary preset palette or not. I'd really rather not have to re-color this entire sheet using the new schemes setup just so that I can maintain a level of consistency here.










share|improve this question























  • Does anyone that works on the sheet use 2003? Would a viable solution be to convert the 2003 colors to a similar color in 2010 that would be in the default colors for 2007 and 2010?

    – Raystafarian
    Jun 7 '13 at 11:12













  • Nobody is using the old version anymore. It would be a decent solution if it weren't for the huge amount of work it'd be to go through and manually change the color for everything to use 2010 theme colors. If only there were someway to globally select all cells by color or something, that might be nice.

    – Darrel Hoffman
    Jun 7 '13 at 13:16











  • @DarrelHoffman Can you base your statement on facts? "Nobody is using the old version anymore" -- What sources back up that claim? I personally know many corporate organisations that are still using Office 2003 and are not ready to switch to newer versions.

    – teylyn
    Jun 7 '13 at 13:57











  • @teylyn I meant in this case. I was responding to Raystafarian's question.

    – Darrel Hoffman
    Jun 7 '13 at 14:46











  • @DarrelHoffman check out this tool - excelcampus.com/tools/color-palette-conversion-2 it might let you automate color conversions so that it doesn't hassle you anymore. I would really update to the new color palette so that all users for this workbook will see the same thing

    – Raystafarian
    Jun 8 '13 at 8:34


















5















I'm working on a spreadsheet which was created in an earlier version of Excel (2003 I think), and it makes extensive use of color-coding for organization. Unfortunately, it was built using the color-scheme from the older version, and basically none of the colors match those in the 2010 theme (except for pure white and pure black). So every time I want to make something conform to the previously existing color-scheme, I either have to choose a custom color and select it by hand, or I have to copy formatting from an existing cell and paste into the target cell (which is annoying because it also copies stuff like borders, etc.)



I've found the Page Layout/Themes/Colors setting, but it seems all I can do there is change the selectable colors to one of their presets, or create a completely new palette - and I only get to select a few colors in that palette, not the full set. It creates the rest by making lighter or darker versions of the colors you choose. Plus, doing either of these things will change the colors already used on the spreadsheet if and only if they exactly match colors used in the previous scheme. This means that things which used to be colored in similar tones to denote their relationship are now colored differently, some remaining in the old scheme and others being automatically changed to the new. It basically breaks the whole system.



What I want is to be able to just add the specific colors I use from the old document so that they appear in the color chooser and I don't have to go searching for them. Older versions of Excel had this capability - there were a bunch of customizable squares that you could make any color you want and they'd be saved with the theme. I think you could even add more squares, I forget. (Don't have the old version installed anymore.) Did they remove this feature in favor of their "Themes"? Or is it hidden away somewhere? I'm not at all interested in this color schemes feature. I just want to use whatever colors I want to use (particularly the ones that match this document) without having to worry about whether they match some arbitrary preset palette or not. I'd really rather not have to re-color this entire sheet using the new schemes setup just so that I can maintain a level of consistency here.










share|improve this question























  • Does anyone that works on the sheet use 2003? Would a viable solution be to convert the 2003 colors to a similar color in 2010 that would be in the default colors for 2007 and 2010?

    – Raystafarian
    Jun 7 '13 at 11:12













  • Nobody is using the old version anymore. It would be a decent solution if it weren't for the huge amount of work it'd be to go through and manually change the color for everything to use 2010 theme colors. If only there were someway to globally select all cells by color or something, that might be nice.

    – Darrel Hoffman
    Jun 7 '13 at 13:16











  • @DarrelHoffman Can you base your statement on facts? "Nobody is using the old version anymore" -- What sources back up that claim? I personally know many corporate organisations that are still using Office 2003 and are not ready to switch to newer versions.

    – teylyn
    Jun 7 '13 at 13:57











  • @teylyn I meant in this case. I was responding to Raystafarian's question.

    – Darrel Hoffman
    Jun 7 '13 at 14:46











  • @DarrelHoffman check out this tool - excelcampus.com/tools/color-palette-conversion-2 it might let you automate color conversions so that it doesn't hassle you anymore. I would really update to the new color palette so that all users for this workbook will see the same thing

    – Raystafarian
    Jun 8 '13 at 8:34














5












5








5








I'm working on a spreadsheet which was created in an earlier version of Excel (2003 I think), and it makes extensive use of color-coding for organization. Unfortunately, it was built using the color-scheme from the older version, and basically none of the colors match those in the 2010 theme (except for pure white and pure black). So every time I want to make something conform to the previously existing color-scheme, I either have to choose a custom color and select it by hand, or I have to copy formatting from an existing cell and paste into the target cell (which is annoying because it also copies stuff like borders, etc.)



I've found the Page Layout/Themes/Colors setting, but it seems all I can do there is change the selectable colors to one of their presets, or create a completely new palette - and I only get to select a few colors in that palette, not the full set. It creates the rest by making lighter or darker versions of the colors you choose. Plus, doing either of these things will change the colors already used on the spreadsheet if and only if they exactly match colors used in the previous scheme. This means that things which used to be colored in similar tones to denote their relationship are now colored differently, some remaining in the old scheme and others being automatically changed to the new. It basically breaks the whole system.



What I want is to be able to just add the specific colors I use from the old document so that they appear in the color chooser and I don't have to go searching for them. Older versions of Excel had this capability - there were a bunch of customizable squares that you could make any color you want and they'd be saved with the theme. I think you could even add more squares, I forget. (Don't have the old version installed anymore.) Did they remove this feature in favor of their "Themes"? Or is it hidden away somewhere? I'm not at all interested in this color schemes feature. I just want to use whatever colors I want to use (particularly the ones that match this document) without having to worry about whether they match some arbitrary preset palette or not. I'd really rather not have to re-color this entire sheet using the new schemes setup just so that I can maintain a level of consistency here.










share|improve this question














I'm working on a spreadsheet which was created in an earlier version of Excel (2003 I think), and it makes extensive use of color-coding for organization. Unfortunately, it was built using the color-scheme from the older version, and basically none of the colors match those in the 2010 theme (except for pure white and pure black). So every time I want to make something conform to the previously existing color-scheme, I either have to choose a custom color and select it by hand, or I have to copy formatting from an existing cell and paste into the target cell (which is annoying because it also copies stuff like borders, etc.)



I've found the Page Layout/Themes/Colors setting, but it seems all I can do there is change the selectable colors to one of their presets, or create a completely new palette - and I only get to select a few colors in that palette, not the full set. It creates the rest by making lighter or darker versions of the colors you choose. Plus, doing either of these things will change the colors already used on the spreadsheet if and only if they exactly match colors used in the previous scheme. This means that things which used to be colored in similar tones to denote their relationship are now colored differently, some remaining in the old scheme and others being automatically changed to the new. It basically breaks the whole system.



What I want is to be able to just add the specific colors I use from the old document so that they appear in the color chooser and I don't have to go searching for them. Older versions of Excel had this capability - there were a bunch of customizable squares that you could make any color you want and they'd be saved with the theme. I think you could even add more squares, I forget. (Don't have the old version installed anymore.) Did they remove this feature in favor of their "Themes"? Or is it hidden away somewhere? I'm not at all interested in this color schemes feature. I just want to use whatever colors I want to use (particularly the ones that match this document) without having to worry about whether they match some arbitrary preset palette or not. I'd really rather not have to re-color this entire sheet using the new schemes setup just so that I can maintain a level of consistency here.







microsoft-excel microsoft-excel-2010 colors






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Jun 7 '13 at 7:24









Darrel HoffmanDarrel Hoffman

1522310




1522310













  • Does anyone that works on the sheet use 2003? Would a viable solution be to convert the 2003 colors to a similar color in 2010 that would be in the default colors for 2007 and 2010?

    – Raystafarian
    Jun 7 '13 at 11:12













  • Nobody is using the old version anymore. It would be a decent solution if it weren't for the huge amount of work it'd be to go through and manually change the color for everything to use 2010 theme colors. If only there were someway to globally select all cells by color or something, that might be nice.

    – Darrel Hoffman
    Jun 7 '13 at 13:16











  • @DarrelHoffman Can you base your statement on facts? "Nobody is using the old version anymore" -- What sources back up that claim? I personally know many corporate organisations that are still using Office 2003 and are not ready to switch to newer versions.

    – teylyn
    Jun 7 '13 at 13:57











  • @teylyn I meant in this case. I was responding to Raystafarian's question.

    – Darrel Hoffman
    Jun 7 '13 at 14:46











  • @DarrelHoffman check out this tool - excelcampus.com/tools/color-palette-conversion-2 it might let you automate color conversions so that it doesn't hassle you anymore. I would really update to the new color palette so that all users for this workbook will see the same thing

    – Raystafarian
    Jun 8 '13 at 8:34



















  • Does anyone that works on the sheet use 2003? Would a viable solution be to convert the 2003 colors to a similar color in 2010 that would be in the default colors for 2007 and 2010?

    – Raystafarian
    Jun 7 '13 at 11:12













  • Nobody is using the old version anymore. It would be a decent solution if it weren't for the huge amount of work it'd be to go through and manually change the color for everything to use 2010 theme colors. If only there were someway to globally select all cells by color or something, that might be nice.

    – Darrel Hoffman
    Jun 7 '13 at 13:16











  • @DarrelHoffman Can you base your statement on facts? "Nobody is using the old version anymore" -- What sources back up that claim? I personally know many corporate organisations that are still using Office 2003 and are not ready to switch to newer versions.

    – teylyn
    Jun 7 '13 at 13:57











  • @teylyn I meant in this case. I was responding to Raystafarian's question.

    – Darrel Hoffman
    Jun 7 '13 at 14:46











  • @DarrelHoffman check out this tool - excelcampus.com/tools/color-palette-conversion-2 it might let you automate color conversions so that it doesn't hassle you anymore. I would really update to the new color palette so that all users for this workbook will see the same thing

    – Raystafarian
    Jun 8 '13 at 8:34

















Does anyone that works on the sheet use 2003? Would a viable solution be to convert the 2003 colors to a similar color in 2010 that would be in the default colors for 2007 and 2010?

– Raystafarian
Jun 7 '13 at 11:12







Does anyone that works on the sheet use 2003? Would a viable solution be to convert the 2003 colors to a similar color in 2010 that would be in the default colors for 2007 and 2010?

– Raystafarian
Jun 7 '13 at 11:12















Nobody is using the old version anymore. It would be a decent solution if it weren't for the huge amount of work it'd be to go through and manually change the color for everything to use 2010 theme colors. If only there were someway to globally select all cells by color or something, that might be nice.

– Darrel Hoffman
Jun 7 '13 at 13:16





Nobody is using the old version anymore. It would be a decent solution if it weren't for the huge amount of work it'd be to go through and manually change the color for everything to use 2010 theme colors. If only there were someway to globally select all cells by color or something, that might be nice.

– Darrel Hoffman
Jun 7 '13 at 13:16













@DarrelHoffman Can you base your statement on facts? "Nobody is using the old version anymore" -- What sources back up that claim? I personally know many corporate organisations that are still using Office 2003 and are not ready to switch to newer versions.

– teylyn
Jun 7 '13 at 13:57





@DarrelHoffman Can you base your statement on facts? "Nobody is using the old version anymore" -- What sources back up that claim? I personally know many corporate organisations that are still using Office 2003 and are not ready to switch to newer versions.

– teylyn
Jun 7 '13 at 13:57













@teylyn I meant in this case. I was responding to Raystafarian's question.

– Darrel Hoffman
Jun 7 '13 at 14:46





@teylyn I meant in this case. I was responding to Raystafarian's question.

– Darrel Hoffman
Jun 7 '13 at 14:46













@DarrelHoffman check out this tool - excelcampus.com/tools/color-palette-conversion-2 it might let you automate color conversions so that it doesn't hassle you anymore. I would really update to the new color palette so that all users for this workbook will see the same thing

– Raystafarian
Jun 8 '13 at 8:34





@DarrelHoffman check out this tool - excelcampus.com/tools/color-palette-conversion-2 it might let you automate color conversions so that it doesn't hassle you anymore. I would really update to the new color palette so that all users for this workbook will see the same thing

– Raystafarian
Jun 8 '13 at 8:34










3 Answers
3






active

oldest

votes


















4














Hmm, I'm afraid you're out of luck.



In Excel 2003, there was a colour palette with 40 customisable colours for worksheet use and 16 additional colours for chart use. The default palette settings could be customised and changed with the file, chart colours could be used in worksheet cells and vice versa.



enter image description here



Starting with Office 2007, this principle was replaced with the "theme" colour, which consists of two text and six accent colours and different intensities of these to choose from. Themes are consistent across all Office applications. It is easy enough to switch between themes and items that have been formatted with theme colours will change when the theme is switched.



It is also easy enough to create a new theme with your favourite colour choices, if they don't exceed the 6 accent colours.



There is always the possibility to veer from the theme colours and pick a custom colour, from the palette of 127 standard colours and several shades of gray, or by defining custom colours with RGB or HSL values. Unfortunately, there is no easy way to add such a custom selection to the standard palette.



What is extremely difficult, though, is to define your custom colour palette with more than the two text and 6 accent colours, i.e. anything similar to the 56 colours that Excel offered before version 2007.



Are you aware that Excel has styles, just like Word? Excel styles can include font, font size, number formatting, text colour and fill colour. You could make use of the Excel styles feature and create different styles with exactly the colouring and other formatting you want for a cell.



Another way (although not easy and a bit of work) would be to create a new sheet, use two grids of 8 columns by 7 rows and manually set the colour of each cell to the RGB values as the original Excel 2003 file has. Use one of the grids for the fill colour, one of the grids for the text colour. Then you can select a cell with the desired color, copy it and paste its formatting in the target cell.



Or, copy and paste each of your distinct cell formatting into a kind of style guide table on a new sheet, and use it to copy and paste formats only.



Ultimately, you will want to shift your thinking to the themes principle, though. The 56 individual colours are gone from the user interface since Office 2007. Tone on tone color schemes are the rage instead, with shades of six accent colours.



If you start designing new spreadsheets along these lines, your life will be easier in the long run.






share|improve this answer
























  • Damn - why would they remove such a useful feature? I get adding themes and changeable color-schemes across multiple applications and all that - yay, fancy. But why remove the old features in the process, and thereby make it a total pain to work with older documents? And the new system doesn't even have more colors, it has less. (Yeah, okay for new spreadsheets, but totally sucks for dealing with old ones.)

    – Darrel Hoffman
    Jun 7 '13 at 13:20






  • 3





    "Why would they..."? Because the possible uses of Office are manifold and the marketing goals of Microsoft don't always match the functional requirements of every single Office user. As an MVP I get to trial new versions early, before public release. Be assured that people like me give permanent feedback to Microsoft about (non-)usability of features during alpha and beta of new versions. Our concerns get noted, but that does not mean that all of our suggestions get actioned and make it into the final release. ...

    – teylyn
    Jun 7 '13 at 13:50








  • 2





    ... As with so many things, it's a balancing act of cost (to create the functionality) and benefit (how many folks will use it and will want to pay for it). Most people only ever used 2003 standard colours and did not even change the abysmal gray chart background. Now these people are just as happy to use themes. Most don't even realise that something has changed. ...

    – teylyn
    Jun 7 '13 at 13:51








  • 1





    ... So there is no business case. Not enough financial benefit to put a development team through a 2-year project to develop a colour mapping between 2003 and later versions. I hear you and I can feel your frustration. But the theme coloring is just one thing we all had to learn to live with after 2007 was launched. It's actually not all that bad, if you can disregard legacy files.

    – teylyn
    Jun 7 '13 at 13:51








  • 2





    But yeah - legacy files seem like an important enough reason to me not to remove features. Adding features is fine, but removing them is generally bad if it makes working with older files a hassle. I can't think of any other case where a useful feature has been removed like that. Hidden maybe, but not discarded entirely. Just seems like a step backward. Oh well...

    – Darrel Hoffman
    Jun 7 '13 at 14:51



















4














You could try using (or adapting) this spreadsheet published by John Walkenbach. This technique will create a button on the ribbon and give you a palette of Excel 2003 colours.



enter image description here




  1. Download the sample workbook ColorPicker.xls


  2. Try out the button labelled Click here to change the background color of the selected cells



    J-Walk's ColorPicker



  3. Save the file somewhere.

  4. Right-click anywhere on the ribbon and select Customise the Ribbon.


  5. From the Choose commands from dropdown select Macros then select ...GetAColor2 below (highlighted yellow in screengrab).



    enter image description here




  6. In the right hand panel, click on Home then select New Group. Right-click on the New Group (Custom) option that appears and rename it 2003 Colours and pick the fill icon.



    enter image description here




  7. Finally click the Add >> button in the centre. Rename the new option 2003 Colours and pick the fill icon again. You should now be able to see it in the ribbon.



    enter image description here



  8. You may prefer to move the VBA from this file into your personal workbook so you're not opening an external file when clicking the button, or maybe use a keyboard-shortcut instead of a ribbon button. But as a quick way to get up and running this works well.



Note - more information on adapting J-Walk's workbook here.






share|improve this answer

































    0














    This solution worked for me in Excel 2013 and so I post it here for anyone that stumbles across this question in the future and don't like the "you can't" answer.





    If you are making new files:



    Create a new template that Excel will use whenever it creates new files.




    1. Open a blank workbook

    2. Customize it as desired (All future new workbooks will be a copy of this one so take your time to set your desired font settings, column widths, whatever)

    3. For each of the custom colors you want saves, set a cell to that custom color. (I just used A1 over and over)

    4. Set all your cells back to No Fill

    5. Now that you have the colors you want showing in your Recent Colors, you can save the template.

    6. Save the file as "Book.xltx" in Excel's startup folder. For me, this was
      C:users{username}AppDataRoamingMicrosoftExcelXLSTART

    7. Close the file because it won't let you open a new file based on the template while the template is still open

    8. Open a new file to see if it worked


    When I got to step 8, my new file had the color options shown below:



    Colors





    If you are editing old files:



    Automate step 3 above using VBA.




    1. Store the VBA below in your personal macro workbook

    2. Edit the ColorList variable to be a list of the RGB values you need

    3. Edit the sleep times if needed for your particular setup

    4. Add the macro to your ribbon


    (If those links rot, the issues are common enough that you should be able to find a plethora of help via your preferred web search engine.)



     





    'Declare Sleep() API
    #If VBA7 Then ' Excel 2010 or later
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
    #Else ' Excel 2007 or earlier
    Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
    #End If

    Sub LoadRecentColors()
    'PURPOSE: Use A List Of RGB Codes To Load Colors Into Recent Colors Section of Color Palette
    'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

    Dim ColorList As Variant
    Dim CurrentFill As Variant

    'Array List of RGB Color Codes to Add To Recent Colors Section (Max 10)
    ColorList = Array("066,174,093", "184,055,038", "046,062,081", "056,160,133")

    'Store ActiveCell's Fill Color (if applicable)
    If ActiveCell.Interior.ColorIndex <> xlNone Then CurrentFill = ActiveCell.Interior.Color

    'Optimize Code
    Application.ScreenUpdating = False

    'Loop Through List Of RGB Codes And Add To Recent Colors
    For x = LBound(ColorList) To UBound(ColorList)
    ActiveCell.Interior.Color = RGB(Left(ColorList(x), 3), Mid(ColorList(x), 5, 3), Right(ColorList(x), 3))
    DoEvents
    SendKeys "%h"
    Sleep 500 'Pause half-second (units in milliseconds)
    SendKeys "h"
    Sleep 500 'Pause half-second (units in milliseconds)
    SendKeys "m"
    Sleep 500 'Pause half-second (units in milliseconds)
    SendKeys "~"
    Sleep 500 'Pause half-second (units in milliseconds)
    DoEvents
    Next x

    'Return ActiveCell Original Fill Color
    If CurrentFill = Empty Then
    ActiveCell.Interior.ColorIndex = xlNone
    Else
    ActiveCell.Interior.Color = currentColor
    End If

    End Sub


    Original Source for VBA






    share|improve this answer


























    • Works if you're creating new files all the time, but not so much when you're updating files that are already created. That said, this question is 6 years old and 4 jobs ago, and I don't even remember what project I was working on back then...

      – Darrel Hoffman
      Feb 11 at 20:28











    • @DarrelHoffman There's a VBA solution to quickly add the custom colors to any given file by automating step 3 which I can edit in. I noted the age but, since I came across this question is my search to do exactly this, I posted the solution for the next person seeking the wisdom of the ancients.

      – Engineer Toast
      Feb 11 at 21:26












    protected by Community May 7 '14 at 12:05



    Thank you for your interest in this question.
    Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).



    Would you like to answer one of these unanswered questions instead?














    3 Answers
    3






    active

    oldest

    votes








    3 Answers
    3






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    4














    Hmm, I'm afraid you're out of luck.



    In Excel 2003, there was a colour palette with 40 customisable colours for worksheet use and 16 additional colours for chart use. The default palette settings could be customised and changed with the file, chart colours could be used in worksheet cells and vice versa.



    enter image description here



    Starting with Office 2007, this principle was replaced with the "theme" colour, which consists of two text and six accent colours and different intensities of these to choose from. Themes are consistent across all Office applications. It is easy enough to switch between themes and items that have been formatted with theme colours will change when the theme is switched.



    It is also easy enough to create a new theme with your favourite colour choices, if they don't exceed the 6 accent colours.



    There is always the possibility to veer from the theme colours and pick a custom colour, from the palette of 127 standard colours and several shades of gray, or by defining custom colours with RGB or HSL values. Unfortunately, there is no easy way to add such a custom selection to the standard palette.



    What is extremely difficult, though, is to define your custom colour palette with more than the two text and 6 accent colours, i.e. anything similar to the 56 colours that Excel offered before version 2007.



    Are you aware that Excel has styles, just like Word? Excel styles can include font, font size, number formatting, text colour and fill colour. You could make use of the Excel styles feature and create different styles with exactly the colouring and other formatting you want for a cell.



    Another way (although not easy and a bit of work) would be to create a new sheet, use two grids of 8 columns by 7 rows and manually set the colour of each cell to the RGB values as the original Excel 2003 file has. Use one of the grids for the fill colour, one of the grids for the text colour. Then you can select a cell with the desired color, copy it and paste its formatting in the target cell.



    Or, copy and paste each of your distinct cell formatting into a kind of style guide table on a new sheet, and use it to copy and paste formats only.



    Ultimately, you will want to shift your thinking to the themes principle, though. The 56 individual colours are gone from the user interface since Office 2007. Tone on tone color schemes are the rage instead, with shades of six accent colours.



    If you start designing new spreadsheets along these lines, your life will be easier in the long run.






    share|improve this answer
























    • Damn - why would they remove such a useful feature? I get adding themes and changeable color-schemes across multiple applications and all that - yay, fancy. But why remove the old features in the process, and thereby make it a total pain to work with older documents? And the new system doesn't even have more colors, it has less. (Yeah, okay for new spreadsheets, but totally sucks for dealing with old ones.)

      – Darrel Hoffman
      Jun 7 '13 at 13:20






    • 3





      "Why would they..."? Because the possible uses of Office are manifold and the marketing goals of Microsoft don't always match the functional requirements of every single Office user. As an MVP I get to trial new versions early, before public release. Be assured that people like me give permanent feedback to Microsoft about (non-)usability of features during alpha and beta of new versions. Our concerns get noted, but that does not mean that all of our suggestions get actioned and make it into the final release. ...

      – teylyn
      Jun 7 '13 at 13:50








    • 2





      ... As with so many things, it's a balancing act of cost (to create the functionality) and benefit (how many folks will use it and will want to pay for it). Most people only ever used 2003 standard colours and did not even change the abysmal gray chart background. Now these people are just as happy to use themes. Most don't even realise that something has changed. ...

      – teylyn
      Jun 7 '13 at 13:51








    • 1





      ... So there is no business case. Not enough financial benefit to put a development team through a 2-year project to develop a colour mapping between 2003 and later versions. I hear you and I can feel your frustration. But the theme coloring is just one thing we all had to learn to live with after 2007 was launched. It's actually not all that bad, if you can disregard legacy files.

      – teylyn
      Jun 7 '13 at 13:51








    • 2





      But yeah - legacy files seem like an important enough reason to me not to remove features. Adding features is fine, but removing them is generally bad if it makes working with older files a hassle. I can't think of any other case where a useful feature has been removed like that. Hidden maybe, but not discarded entirely. Just seems like a step backward. Oh well...

      – Darrel Hoffman
      Jun 7 '13 at 14:51
















    4














    Hmm, I'm afraid you're out of luck.



    In Excel 2003, there was a colour palette with 40 customisable colours for worksheet use and 16 additional colours for chart use. The default palette settings could be customised and changed with the file, chart colours could be used in worksheet cells and vice versa.



    enter image description here



    Starting with Office 2007, this principle was replaced with the "theme" colour, which consists of two text and six accent colours and different intensities of these to choose from. Themes are consistent across all Office applications. It is easy enough to switch between themes and items that have been formatted with theme colours will change when the theme is switched.



    It is also easy enough to create a new theme with your favourite colour choices, if they don't exceed the 6 accent colours.



    There is always the possibility to veer from the theme colours and pick a custom colour, from the palette of 127 standard colours and several shades of gray, or by defining custom colours with RGB or HSL values. Unfortunately, there is no easy way to add such a custom selection to the standard palette.



    What is extremely difficult, though, is to define your custom colour palette with more than the two text and 6 accent colours, i.e. anything similar to the 56 colours that Excel offered before version 2007.



    Are you aware that Excel has styles, just like Word? Excel styles can include font, font size, number formatting, text colour and fill colour. You could make use of the Excel styles feature and create different styles with exactly the colouring and other formatting you want for a cell.



    Another way (although not easy and a bit of work) would be to create a new sheet, use two grids of 8 columns by 7 rows and manually set the colour of each cell to the RGB values as the original Excel 2003 file has. Use one of the grids for the fill colour, one of the grids for the text colour. Then you can select a cell with the desired color, copy it and paste its formatting in the target cell.



    Or, copy and paste each of your distinct cell formatting into a kind of style guide table on a new sheet, and use it to copy and paste formats only.



    Ultimately, you will want to shift your thinking to the themes principle, though. The 56 individual colours are gone from the user interface since Office 2007. Tone on tone color schemes are the rage instead, with shades of six accent colours.



    If you start designing new spreadsheets along these lines, your life will be easier in the long run.






    share|improve this answer
























    • Damn - why would they remove such a useful feature? I get adding themes and changeable color-schemes across multiple applications and all that - yay, fancy. But why remove the old features in the process, and thereby make it a total pain to work with older documents? And the new system doesn't even have more colors, it has less. (Yeah, okay for new spreadsheets, but totally sucks for dealing with old ones.)

      – Darrel Hoffman
      Jun 7 '13 at 13:20






    • 3





      "Why would they..."? Because the possible uses of Office are manifold and the marketing goals of Microsoft don't always match the functional requirements of every single Office user. As an MVP I get to trial new versions early, before public release. Be assured that people like me give permanent feedback to Microsoft about (non-)usability of features during alpha and beta of new versions. Our concerns get noted, but that does not mean that all of our suggestions get actioned and make it into the final release. ...

      – teylyn
      Jun 7 '13 at 13:50








    • 2





      ... As with so many things, it's a balancing act of cost (to create the functionality) and benefit (how many folks will use it and will want to pay for it). Most people only ever used 2003 standard colours and did not even change the abysmal gray chart background. Now these people are just as happy to use themes. Most don't even realise that something has changed. ...

      – teylyn
      Jun 7 '13 at 13:51








    • 1





      ... So there is no business case. Not enough financial benefit to put a development team through a 2-year project to develop a colour mapping between 2003 and later versions. I hear you and I can feel your frustration. But the theme coloring is just one thing we all had to learn to live with after 2007 was launched. It's actually not all that bad, if you can disregard legacy files.

      – teylyn
      Jun 7 '13 at 13:51








    • 2





      But yeah - legacy files seem like an important enough reason to me not to remove features. Adding features is fine, but removing them is generally bad if it makes working with older files a hassle. I can't think of any other case where a useful feature has been removed like that. Hidden maybe, but not discarded entirely. Just seems like a step backward. Oh well...

      – Darrel Hoffman
      Jun 7 '13 at 14:51














    4












    4








    4







    Hmm, I'm afraid you're out of luck.



    In Excel 2003, there was a colour palette with 40 customisable colours for worksheet use and 16 additional colours for chart use. The default palette settings could be customised and changed with the file, chart colours could be used in worksheet cells and vice versa.



    enter image description here



    Starting with Office 2007, this principle was replaced with the "theme" colour, which consists of two text and six accent colours and different intensities of these to choose from. Themes are consistent across all Office applications. It is easy enough to switch between themes and items that have been formatted with theme colours will change when the theme is switched.



    It is also easy enough to create a new theme with your favourite colour choices, if they don't exceed the 6 accent colours.



    There is always the possibility to veer from the theme colours and pick a custom colour, from the palette of 127 standard colours and several shades of gray, or by defining custom colours with RGB or HSL values. Unfortunately, there is no easy way to add such a custom selection to the standard palette.



    What is extremely difficult, though, is to define your custom colour palette with more than the two text and 6 accent colours, i.e. anything similar to the 56 colours that Excel offered before version 2007.



    Are you aware that Excel has styles, just like Word? Excel styles can include font, font size, number formatting, text colour and fill colour. You could make use of the Excel styles feature and create different styles with exactly the colouring and other formatting you want for a cell.



    Another way (although not easy and a bit of work) would be to create a new sheet, use two grids of 8 columns by 7 rows and manually set the colour of each cell to the RGB values as the original Excel 2003 file has. Use one of the grids for the fill colour, one of the grids for the text colour. Then you can select a cell with the desired color, copy it and paste its formatting in the target cell.



    Or, copy and paste each of your distinct cell formatting into a kind of style guide table on a new sheet, and use it to copy and paste formats only.



    Ultimately, you will want to shift your thinking to the themes principle, though. The 56 individual colours are gone from the user interface since Office 2007. Tone on tone color schemes are the rage instead, with shades of six accent colours.



    If you start designing new spreadsheets along these lines, your life will be easier in the long run.






    share|improve this answer













    Hmm, I'm afraid you're out of luck.



    In Excel 2003, there was a colour palette with 40 customisable colours for worksheet use and 16 additional colours for chart use. The default palette settings could be customised and changed with the file, chart colours could be used in worksheet cells and vice versa.



    enter image description here



    Starting with Office 2007, this principle was replaced with the "theme" colour, which consists of two text and six accent colours and different intensities of these to choose from. Themes are consistent across all Office applications. It is easy enough to switch between themes and items that have been formatted with theme colours will change when the theme is switched.



    It is also easy enough to create a new theme with your favourite colour choices, if they don't exceed the 6 accent colours.



    There is always the possibility to veer from the theme colours and pick a custom colour, from the palette of 127 standard colours and several shades of gray, or by defining custom colours with RGB or HSL values. Unfortunately, there is no easy way to add such a custom selection to the standard palette.



    What is extremely difficult, though, is to define your custom colour palette with more than the two text and 6 accent colours, i.e. anything similar to the 56 colours that Excel offered before version 2007.



    Are you aware that Excel has styles, just like Word? Excel styles can include font, font size, number formatting, text colour and fill colour. You could make use of the Excel styles feature and create different styles with exactly the colouring and other formatting you want for a cell.



    Another way (although not easy and a bit of work) would be to create a new sheet, use two grids of 8 columns by 7 rows and manually set the colour of each cell to the RGB values as the original Excel 2003 file has. Use one of the grids for the fill colour, one of the grids for the text colour. Then you can select a cell with the desired color, copy it and paste its formatting in the target cell.



    Or, copy and paste each of your distinct cell formatting into a kind of style guide table on a new sheet, and use it to copy and paste formats only.



    Ultimately, you will want to shift your thinking to the themes principle, though. The 56 individual colours are gone from the user interface since Office 2007. Tone on tone color schemes are the rage instead, with shades of six accent colours.



    If you start designing new spreadsheets along these lines, your life will be easier in the long run.







    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Jun 7 '13 at 9:29









    teylynteylyn

    17.5k22539




    17.5k22539













    • Damn - why would they remove such a useful feature? I get adding themes and changeable color-schemes across multiple applications and all that - yay, fancy. But why remove the old features in the process, and thereby make it a total pain to work with older documents? And the new system doesn't even have more colors, it has less. (Yeah, okay for new spreadsheets, but totally sucks for dealing with old ones.)

      – Darrel Hoffman
      Jun 7 '13 at 13:20






    • 3





      "Why would they..."? Because the possible uses of Office are manifold and the marketing goals of Microsoft don't always match the functional requirements of every single Office user. As an MVP I get to trial new versions early, before public release. Be assured that people like me give permanent feedback to Microsoft about (non-)usability of features during alpha and beta of new versions. Our concerns get noted, but that does not mean that all of our suggestions get actioned and make it into the final release. ...

      – teylyn
      Jun 7 '13 at 13:50








    • 2





      ... As with so many things, it's a balancing act of cost (to create the functionality) and benefit (how many folks will use it and will want to pay for it). Most people only ever used 2003 standard colours and did not even change the abysmal gray chart background. Now these people are just as happy to use themes. Most don't even realise that something has changed. ...

      – teylyn
      Jun 7 '13 at 13:51








    • 1





      ... So there is no business case. Not enough financial benefit to put a development team through a 2-year project to develop a colour mapping between 2003 and later versions. I hear you and I can feel your frustration. But the theme coloring is just one thing we all had to learn to live with after 2007 was launched. It's actually not all that bad, if you can disregard legacy files.

      – teylyn
      Jun 7 '13 at 13:51








    • 2





      But yeah - legacy files seem like an important enough reason to me not to remove features. Adding features is fine, but removing them is generally bad if it makes working with older files a hassle. I can't think of any other case where a useful feature has been removed like that. Hidden maybe, but not discarded entirely. Just seems like a step backward. Oh well...

      – Darrel Hoffman
      Jun 7 '13 at 14:51



















    • Damn - why would they remove such a useful feature? I get adding themes and changeable color-schemes across multiple applications and all that - yay, fancy. But why remove the old features in the process, and thereby make it a total pain to work with older documents? And the new system doesn't even have more colors, it has less. (Yeah, okay for new spreadsheets, but totally sucks for dealing with old ones.)

      – Darrel Hoffman
      Jun 7 '13 at 13:20






    • 3





      "Why would they..."? Because the possible uses of Office are manifold and the marketing goals of Microsoft don't always match the functional requirements of every single Office user. As an MVP I get to trial new versions early, before public release. Be assured that people like me give permanent feedback to Microsoft about (non-)usability of features during alpha and beta of new versions. Our concerns get noted, but that does not mean that all of our suggestions get actioned and make it into the final release. ...

      – teylyn
      Jun 7 '13 at 13:50








    • 2





      ... As with so many things, it's a balancing act of cost (to create the functionality) and benefit (how many folks will use it and will want to pay for it). Most people only ever used 2003 standard colours and did not even change the abysmal gray chart background. Now these people are just as happy to use themes. Most don't even realise that something has changed. ...

      – teylyn
      Jun 7 '13 at 13:51








    • 1





      ... So there is no business case. Not enough financial benefit to put a development team through a 2-year project to develop a colour mapping between 2003 and later versions. I hear you and I can feel your frustration. But the theme coloring is just one thing we all had to learn to live with after 2007 was launched. It's actually not all that bad, if you can disregard legacy files.

      – teylyn
      Jun 7 '13 at 13:51








    • 2





      But yeah - legacy files seem like an important enough reason to me not to remove features. Adding features is fine, but removing them is generally bad if it makes working with older files a hassle. I can't think of any other case where a useful feature has been removed like that. Hidden maybe, but not discarded entirely. Just seems like a step backward. Oh well...

      – Darrel Hoffman
      Jun 7 '13 at 14:51

















    Damn - why would they remove such a useful feature? I get adding themes and changeable color-schemes across multiple applications and all that - yay, fancy. But why remove the old features in the process, and thereby make it a total pain to work with older documents? And the new system doesn't even have more colors, it has less. (Yeah, okay for new spreadsheets, but totally sucks for dealing with old ones.)

    – Darrel Hoffman
    Jun 7 '13 at 13:20





    Damn - why would they remove such a useful feature? I get adding themes and changeable color-schemes across multiple applications and all that - yay, fancy. But why remove the old features in the process, and thereby make it a total pain to work with older documents? And the new system doesn't even have more colors, it has less. (Yeah, okay for new spreadsheets, but totally sucks for dealing with old ones.)

    – Darrel Hoffman
    Jun 7 '13 at 13:20




    3




    3





    "Why would they..."? Because the possible uses of Office are manifold and the marketing goals of Microsoft don't always match the functional requirements of every single Office user. As an MVP I get to trial new versions early, before public release. Be assured that people like me give permanent feedback to Microsoft about (non-)usability of features during alpha and beta of new versions. Our concerns get noted, but that does not mean that all of our suggestions get actioned and make it into the final release. ...

    – teylyn
    Jun 7 '13 at 13:50







    "Why would they..."? Because the possible uses of Office are manifold and the marketing goals of Microsoft don't always match the functional requirements of every single Office user. As an MVP I get to trial new versions early, before public release. Be assured that people like me give permanent feedback to Microsoft about (non-)usability of features during alpha and beta of new versions. Our concerns get noted, but that does not mean that all of our suggestions get actioned and make it into the final release. ...

    – teylyn
    Jun 7 '13 at 13:50






    2




    2





    ... As with so many things, it's a balancing act of cost (to create the functionality) and benefit (how many folks will use it and will want to pay for it). Most people only ever used 2003 standard colours and did not even change the abysmal gray chart background. Now these people are just as happy to use themes. Most don't even realise that something has changed. ...

    – teylyn
    Jun 7 '13 at 13:51







    ... As with so many things, it's a balancing act of cost (to create the functionality) and benefit (how many folks will use it and will want to pay for it). Most people only ever used 2003 standard colours and did not even change the abysmal gray chart background. Now these people are just as happy to use themes. Most don't even realise that something has changed. ...

    – teylyn
    Jun 7 '13 at 13:51






    1




    1





    ... So there is no business case. Not enough financial benefit to put a development team through a 2-year project to develop a colour mapping between 2003 and later versions. I hear you and I can feel your frustration. But the theme coloring is just one thing we all had to learn to live with after 2007 was launched. It's actually not all that bad, if you can disregard legacy files.

    – teylyn
    Jun 7 '13 at 13:51







    ... So there is no business case. Not enough financial benefit to put a development team through a 2-year project to develop a colour mapping between 2003 and later versions. I hear you and I can feel your frustration. But the theme coloring is just one thing we all had to learn to live with after 2007 was launched. It's actually not all that bad, if you can disregard legacy files.

    – teylyn
    Jun 7 '13 at 13:51






    2




    2





    But yeah - legacy files seem like an important enough reason to me not to remove features. Adding features is fine, but removing them is generally bad if it makes working with older files a hassle. I can't think of any other case where a useful feature has been removed like that. Hidden maybe, but not discarded entirely. Just seems like a step backward. Oh well...

    – Darrel Hoffman
    Jun 7 '13 at 14:51





    But yeah - legacy files seem like an important enough reason to me not to remove features. Adding features is fine, but removing them is generally bad if it makes working with older files a hassle. I can't think of any other case where a useful feature has been removed like that. Hidden maybe, but not discarded entirely. Just seems like a step backward. Oh well...

    – Darrel Hoffman
    Jun 7 '13 at 14:51













    4














    You could try using (or adapting) this spreadsheet published by John Walkenbach. This technique will create a button on the ribbon and give you a palette of Excel 2003 colours.



    enter image description here




    1. Download the sample workbook ColorPicker.xls


    2. Try out the button labelled Click here to change the background color of the selected cells



      J-Walk's ColorPicker



    3. Save the file somewhere.

    4. Right-click anywhere on the ribbon and select Customise the Ribbon.


    5. From the Choose commands from dropdown select Macros then select ...GetAColor2 below (highlighted yellow in screengrab).



      enter image description here




    6. In the right hand panel, click on Home then select New Group. Right-click on the New Group (Custom) option that appears and rename it 2003 Colours and pick the fill icon.



      enter image description here




    7. Finally click the Add >> button in the centre. Rename the new option 2003 Colours and pick the fill icon again. You should now be able to see it in the ribbon.



      enter image description here



    8. You may prefer to move the VBA from this file into your personal workbook so you're not opening an external file when clicking the button, or maybe use a keyboard-shortcut instead of a ribbon button. But as a quick way to get up and running this works well.



    Note - more information on adapting J-Walk's workbook here.






    share|improve this answer






























      4














      You could try using (or adapting) this spreadsheet published by John Walkenbach. This technique will create a button on the ribbon and give you a palette of Excel 2003 colours.



      enter image description here




      1. Download the sample workbook ColorPicker.xls


      2. Try out the button labelled Click here to change the background color of the selected cells



        J-Walk's ColorPicker



      3. Save the file somewhere.

      4. Right-click anywhere on the ribbon and select Customise the Ribbon.


      5. From the Choose commands from dropdown select Macros then select ...GetAColor2 below (highlighted yellow in screengrab).



        enter image description here




      6. In the right hand panel, click on Home then select New Group. Right-click on the New Group (Custom) option that appears and rename it 2003 Colours and pick the fill icon.



        enter image description here




      7. Finally click the Add >> button in the centre. Rename the new option 2003 Colours and pick the fill icon again. You should now be able to see it in the ribbon.



        enter image description here



      8. You may prefer to move the VBA from this file into your personal workbook so you're not opening an external file when clicking the button, or maybe use a keyboard-shortcut instead of a ribbon button. But as a quick way to get up and running this works well.



      Note - more information on adapting J-Walk's workbook here.






      share|improve this answer




























        4












        4








        4







        You could try using (or adapting) this spreadsheet published by John Walkenbach. This technique will create a button on the ribbon and give you a palette of Excel 2003 colours.



        enter image description here




        1. Download the sample workbook ColorPicker.xls


        2. Try out the button labelled Click here to change the background color of the selected cells



          J-Walk's ColorPicker



        3. Save the file somewhere.

        4. Right-click anywhere on the ribbon and select Customise the Ribbon.


        5. From the Choose commands from dropdown select Macros then select ...GetAColor2 below (highlighted yellow in screengrab).



          enter image description here




        6. In the right hand panel, click on Home then select New Group. Right-click on the New Group (Custom) option that appears and rename it 2003 Colours and pick the fill icon.



          enter image description here




        7. Finally click the Add >> button in the centre. Rename the new option 2003 Colours and pick the fill icon again. You should now be able to see it in the ribbon.



          enter image description here



        8. You may prefer to move the VBA from this file into your personal workbook so you're not opening an external file when clicking the button, or maybe use a keyboard-shortcut instead of a ribbon button. But as a quick way to get up and running this works well.



        Note - more information on adapting J-Walk's workbook here.






        share|improve this answer















        You could try using (or adapting) this spreadsheet published by John Walkenbach. This technique will create a button on the ribbon and give you a palette of Excel 2003 colours.



        enter image description here




        1. Download the sample workbook ColorPicker.xls


        2. Try out the button labelled Click here to change the background color of the selected cells



          J-Walk's ColorPicker



        3. Save the file somewhere.

        4. Right-click anywhere on the ribbon and select Customise the Ribbon.


        5. From the Choose commands from dropdown select Macros then select ...GetAColor2 below (highlighted yellow in screengrab).



          enter image description here




        6. In the right hand panel, click on Home then select New Group. Right-click on the New Group (Custom) option that appears and rename it 2003 Colours and pick the fill icon.



          enter image description here




        7. Finally click the Add >> button in the centre. Rename the new option 2003 Colours and pick the fill icon again. You should now be able to see it in the ribbon.



          enter image description here



        8. You may prefer to move the VBA from this file into your personal workbook so you're not opening an external file when clicking the button, or maybe use a keyboard-shortcut instead of a ribbon button. But as a quick way to get up and running this works well.



        Note - more information on adapting J-Walk's workbook here.







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited May 2 '14 at 10:35

























        answered May 1 '14 at 17:19









        Andi MohrAndi Mohr

        3,40042144




        3,40042144























            0














            This solution worked for me in Excel 2013 and so I post it here for anyone that stumbles across this question in the future and don't like the "you can't" answer.





            If you are making new files:



            Create a new template that Excel will use whenever it creates new files.




            1. Open a blank workbook

            2. Customize it as desired (All future new workbooks will be a copy of this one so take your time to set your desired font settings, column widths, whatever)

            3. For each of the custom colors you want saves, set a cell to that custom color. (I just used A1 over and over)

            4. Set all your cells back to No Fill

            5. Now that you have the colors you want showing in your Recent Colors, you can save the template.

            6. Save the file as "Book.xltx" in Excel's startup folder. For me, this was
              C:users{username}AppDataRoamingMicrosoftExcelXLSTART

            7. Close the file because it won't let you open a new file based on the template while the template is still open

            8. Open a new file to see if it worked


            When I got to step 8, my new file had the color options shown below:



            Colors





            If you are editing old files:



            Automate step 3 above using VBA.




            1. Store the VBA below in your personal macro workbook

            2. Edit the ColorList variable to be a list of the RGB values you need

            3. Edit the sleep times if needed for your particular setup

            4. Add the macro to your ribbon


            (If those links rot, the issues are common enough that you should be able to find a plethora of help via your preferred web search engine.)



             





            'Declare Sleep() API
            #If VBA7 Then ' Excel 2010 or later
            Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
            #Else ' Excel 2007 or earlier
            Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
            #End If

            Sub LoadRecentColors()
            'PURPOSE: Use A List Of RGB Codes To Load Colors Into Recent Colors Section of Color Palette
            'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

            Dim ColorList As Variant
            Dim CurrentFill As Variant

            'Array List of RGB Color Codes to Add To Recent Colors Section (Max 10)
            ColorList = Array("066,174,093", "184,055,038", "046,062,081", "056,160,133")

            'Store ActiveCell's Fill Color (if applicable)
            If ActiveCell.Interior.ColorIndex <> xlNone Then CurrentFill = ActiveCell.Interior.Color

            'Optimize Code
            Application.ScreenUpdating = False

            'Loop Through List Of RGB Codes And Add To Recent Colors
            For x = LBound(ColorList) To UBound(ColorList)
            ActiveCell.Interior.Color = RGB(Left(ColorList(x), 3), Mid(ColorList(x), 5, 3), Right(ColorList(x), 3))
            DoEvents
            SendKeys "%h"
            Sleep 500 'Pause half-second (units in milliseconds)
            SendKeys "h"
            Sleep 500 'Pause half-second (units in milliseconds)
            SendKeys "m"
            Sleep 500 'Pause half-second (units in milliseconds)
            SendKeys "~"
            Sleep 500 'Pause half-second (units in milliseconds)
            DoEvents
            Next x

            'Return ActiveCell Original Fill Color
            If CurrentFill = Empty Then
            ActiveCell.Interior.ColorIndex = xlNone
            Else
            ActiveCell.Interior.Color = currentColor
            End If

            End Sub


            Original Source for VBA






            share|improve this answer


























            • Works if you're creating new files all the time, but not so much when you're updating files that are already created. That said, this question is 6 years old and 4 jobs ago, and I don't even remember what project I was working on back then...

              – Darrel Hoffman
              Feb 11 at 20:28











            • @DarrelHoffman There's a VBA solution to quickly add the custom colors to any given file by automating step 3 which I can edit in. I noted the age but, since I came across this question is my search to do exactly this, I posted the solution for the next person seeking the wisdom of the ancients.

              – Engineer Toast
              Feb 11 at 21:26


















            0














            This solution worked for me in Excel 2013 and so I post it here for anyone that stumbles across this question in the future and don't like the "you can't" answer.





            If you are making new files:



            Create a new template that Excel will use whenever it creates new files.




            1. Open a blank workbook

            2. Customize it as desired (All future new workbooks will be a copy of this one so take your time to set your desired font settings, column widths, whatever)

            3. For each of the custom colors you want saves, set a cell to that custom color. (I just used A1 over and over)

            4. Set all your cells back to No Fill

            5. Now that you have the colors you want showing in your Recent Colors, you can save the template.

            6. Save the file as "Book.xltx" in Excel's startup folder. For me, this was
              C:users{username}AppDataRoamingMicrosoftExcelXLSTART

            7. Close the file because it won't let you open a new file based on the template while the template is still open

            8. Open a new file to see if it worked


            When I got to step 8, my new file had the color options shown below:



            Colors





            If you are editing old files:



            Automate step 3 above using VBA.




            1. Store the VBA below in your personal macro workbook

            2. Edit the ColorList variable to be a list of the RGB values you need

            3. Edit the sleep times if needed for your particular setup

            4. Add the macro to your ribbon


            (If those links rot, the issues are common enough that you should be able to find a plethora of help via your preferred web search engine.)



             





            'Declare Sleep() API
            #If VBA7 Then ' Excel 2010 or later
            Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
            #Else ' Excel 2007 or earlier
            Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
            #End If

            Sub LoadRecentColors()
            'PURPOSE: Use A List Of RGB Codes To Load Colors Into Recent Colors Section of Color Palette
            'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

            Dim ColorList As Variant
            Dim CurrentFill As Variant

            'Array List of RGB Color Codes to Add To Recent Colors Section (Max 10)
            ColorList = Array("066,174,093", "184,055,038", "046,062,081", "056,160,133")

            'Store ActiveCell's Fill Color (if applicable)
            If ActiveCell.Interior.ColorIndex <> xlNone Then CurrentFill = ActiveCell.Interior.Color

            'Optimize Code
            Application.ScreenUpdating = False

            'Loop Through List Of RGB Codes And Add To Recent Colors
            For x = LBound(ColorList) To UBound(ColorList)
            ActiveCell.Interior.Color = RGB(Left(ColorList(x), 3), Mid(ColorList(x), 5, 3), Right(ColorList(x), 3))
            DoEvents
            SendKeys "%h"
            Sleep 500 'Pause half-second (units in milliseconds)
            SendKeys "h"
            Sleep 500 'Pause half-second (units in milliseconds)
            SendKeys "m"
            Sleep 500 'Pause half-second (units in milliseconds)
            SendKeys "~"
            Sleep 500 'Pause half-second (units in milliseconds)
            DoEvents
            Next x

            'Return ActiveCell Original Fill Color
            If CurrentFill = Empty Then
            ActiveCell.Interior.ColorIndex = xlNone
            Else
            ActiveCell.Interior.Color = currentColor
            End If

            End Sub


            Original Source for VBA






            share|improve this answer


























            • Works if you're creating new files all the time, but not so much when you're updating files that are already created. That said, this question is 6 years old and 4 jobs ago, and I don't even remember what project I was working on back then...

              – Darrel Hoffman
              Feb 11 at 20:28











            • @DarrelHoffman There's a VBA solution to quickly add the custom colors to any given file by automating step 3 which I can edit in. I noted the age but, since I came across this question is my search to do exactly this, I posted the solution for the next person seeking the wisdom of the ancients.

              – Engineer Toast
              Feb 11 at 21:26
















            0












            0








            0







            This solution worked for me in Excel 2013 and so I post it here for anyone that stumbles across this question in the future and don't like the "you can't" answer.





            If you are making new files:



            Create a new template that Excel will use whenever it creates new files.




            1. Open a blank workbook

            2. Customize it as desired (All future new workbooks will be a copy of this one so take your time to set your desired font settings, column widths, whatever)

            3. For each of the custom colors you want saves, set a cell to that custom color. (I just used A1 over and over)

            4. Set all your cells back to No Fill

            5. Now that you have the colors you want showing in your Recent Colors, you can save the template.

            6. Save the file as "Book.xltx" in Excel's startup folder. For me, this was
              C:users{username}AppDataRoamingMicrosoftExcelXLSTART

            7. Close the file because it won't let you open a new file based on the template while the template is still open

            8. Open a new file to see if it worked


            When I got to step 8, my new file had the color options shown below:



            Colors





            If you are editing old files:



            Automate step 3 above using VBA.




            1. Store the VBA below in your personal macro workbook

            2. Edit the ColorList variable to be a list of the RGB values you need

            3. Edit the sleep times if needed for your particular setup

            4. Add the macro to your ribbon


            (If those links rot, the issues are common enough that you should be able to find a plethora of help via your preferred web search engine.)



             





            'Declare Sleep() API
            #If VBA7 Then ' Excel 2010 or later
            Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
            #Else ' Excel 2007 or earlier
            Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
            #End If

            Sub LoadRecentColors()
            'PURPOSE: Use A List Of RGB Codes To Load Colors Into Recent Colors Section of Color Palette
            'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

            Dim ColorList As Variant
            Dim CurrentFill As Variant

            'Array List of RGB Color Codes to Add To Recent Colors Section (Max 10)
            ColorList = Array("066,174,093", "184,055,038", "046,062,081", "056,160,133")

            'Store ActiveCell's Fill Color (if applicable)
            If ActiveCell.Interior.ColorIndex <> xlNone Then CurrentFill = ActiveCell.Interior.Color

            'Optimize Code
            Application.ScreenUpdating = False

            'Loop Through List Of RGB Codes And Add To Recent Colors
            For x = LBound(ColorList) To UBound(ColorList)
            ActiveCell.Interior.Color = RGB(Left(ColorList(x), 3), Mid(ColorList(x), 5, 3), Right(ColorList(x), 3))
            DoEvents
            SendKeys "%h"
            Sleep 500 'Pause half-second (units in milliseconds)
            SendKeys "h"
            Sleep 500 'Pause half-second (units in milliseconds)
            SendKeys "m"
            Sleep 500 'Pause half-second (units in milliseconds)
            SendKeys "~"
            Sleep 500 'Pause half-second (units in milliseconds)
            DoEvents
            Next x

            'Return ActiveCell Original Fill Color
            If CurrentFill = Empty Then
            ActiveCell.Interior.ColorIndex = xlNone
            Else
            ActiveCell.Interior.Color = currentColor
            End If

            End Sub


            Original Source for VBA






            share|improve this answer















            This solution worked for me in Excel 2013 and so I post it here for anyone that stumbles across this question in the future and don't like the "you can't" answer.





            If you are making new files:



            Create a new template that Excel will use whenever it creates new files.




            1. Open a blank workbook

            2. Customize it as desired (All future new workbooks will be a copy of this one so take your time to set your desired font settings, column widths, whatever)

            3. For each of the custom colors you want saves, set a cell to that custom color. (I just used A1 over and over)

            4. Set all your cells back to No Fill

            5. Now that you have the colors you want showing in your Recent Colors, you can save the template.

            6. Save the file as "Book.xltx" in Excel's startup folder. For me, this was
              C:users{username}AppDataRoamingMicrosoftExcelXLSTART

            7. Close the file because it won't let you open a new file based on the template while the template is still open

            8. Open a new file to see if it worked


            When I got to step 8, my new file had the color options shown below:



            Colors





            If you are editing old files:



            Automate step 3 above using VBA.




            1. Store the VBA below in your personal macro workbook

            2. Edit the ColorList variable to be a list of the RGB values you need

            3. Edit the sleep times if needed for your particular setup

            4. Add the macro to your ribbon


            (If those links rot, the issues are common enough that you should be able to find a plethora of help via your preferred web search engine.)



             





            'Declare Sleep() API
            #If VBA7 Then ' Excel 2010 or later
            Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
            #Else ' Excel 2007 or earlier
            Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
            #End If

            Sub LoadRecentColors()
            'PURPOSE: Use A List Of RGB Codes To Load Colors Into Recent Colors Section of Color Palette
            'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

            Dim ColorList As Variant
            Dim CurrentFill As Variant

            'Array List of RGB Color Codes to Add To Recent Colors Section (Max 10)
            ColorList = Array("066,174,093", "184,055,038", "046,062,081", "056,160,133")

            'Store ActiveCell's Fill Color (if applicable)
            If ActiveCell.Interior.ColorIndex <> xlNone Then CurrentFill = ActiveCell.Interior.Color

            'Optimize Code
            Application.ScreenUpdating = False

            'Loop Through List Of RGB Codes And Add To Recent Colors
            For x = LBound(ColorList) To UBound(ColorList)
            ActiveCell.Interior.Color = RGB(Left(ColorList(x), 3), Mid(ColorList(x), 5, 3), Right(ColorList(x), 3))
            DoEvents
            SendKeys "%h"
            Sleep 500 'Pause half-second (units in milliseconds)
            SendKeys "h"
            Sleep 500 'Pause half-second (units in milliseconds)
            SendKeys "m"
            Sleep 500 'Pause half-second (units in milliseconds)
            SendKeys "~"
            Sleep 500 'Pause half-second (units in milliseconds)
            DoEvents
            Next x

            'Return ActiveCell Original Fill Color
            If CurrentFill = Empty Then
            ActiveCell.Interior.ColorIndex = xlNone
            Else
            ActiveCell.Interior.Color = currentColor
            End If

            End Sub


            Original Source for VBA







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Feb 11 at 21:41

























            answered Feb 11 at 17:45









            Engineer ToastEngineer Toast

            2,9481828




            2,9481828













            • Works if you're creating new files all the time, but not so much when you're updating files that are already created. That said, this question is 6 years old and 4 jobs ago, and I don't even remember what project I was working on back then...

              – Darrel Hoffman
              Feb 11 at 20:28











            • @DarrelHoffman There's a VBA solution to quickly add the custom colors to any given file by automating step 3 which I can edit in. I noted the age but, since I came across this question is my search to do exactly this, I posted the solution for the next person seeking the wisdom of the ancients.

              – Engineer Toast
              Feb 11 at 21:26





















            • Works if you're creating new files all the time, but not so much when you're updating files that are already created. That said, this question is 6 years old and 4 jobs ago, and I don't even remember what project I was working on back then...

              – Darrel Hoffman
              Feb 11 at 20:28











            • @DarrelHoffman There's a VBA solution to quickly add the custom colors to any given file by automating step 3 which I can edit in. I noted the age but, since I came across this question is my search to do exactly this, I posted the solution for the next person seeking the wisdom of the ancients.

              – Engineer Toast
              Feb 11 at 21:26



















            Works if you're creating new files all the time, but not so much when you're updating files that are already created. That said, this question is 6 years old and 4 jobs ago, and I don't even remember what project I was working on back then...

            – Darrel Hoffman
            Feb 11 at 20:28





            Works if you're creating new files all the time, but not so much when you're updating files that are already created. That said, this question is 6 years old and 4 jobs ago, and I don't even remember what project I was working on back then...

            – Darrel Hoffman
            Feb 11 at 20:28













            @DarrelHoffman There's a VBA solution to quickly add the custom colors to any given file by automating step 3 which I can edit in. I noted the age but, since I came across this question is my search to do exactly this, I posted the solution for the next person seeking the wisdom of the ancients.

            – Engineer Toast
            Feb 11 at 21:26







            @DarrelHoffman There's a VBA solution to quickly add the custom colors to any given file by automating step 3 which I can edit in. I noted the age but, since I came across this question is my search to do exactly this, I posted the solution for the next person seeking the wisdom of the ancients.

            – Engineer Toast
            Feb 11 at 21:26







            protected by Community May 7 '14 at 12:05



            Thank you for your interest in this question.
            Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).



            Would you like to answer one of these unanswered questions instead?



            Popular posts from this blog

            Сан-Квентин

            8-я гвардейская общевойсковая армия

            Алькесар