Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Nov 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Restrict the number of colors in Excel 2007

    I have one worksheet where the cells are identified by is background color. It means that each cell is manually colored in a set of 11 colors that have been previously choosen. How can I restrict the format cells (fill) to only these 11 colors? Is there any VBA code where I can only choose one of these 11 colors? The pallete in the Format Cells has a lot of colors and itīs easy to choose a wrong color specially when they are similar.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    You can't reduce the number of colours displayed in the Excel palette, but you could edit it so that only the colours you want are there and the other boxes are simply repeats of those colours (or are all an obviously "wrong" colour) - then you cannot select an incorrect colour.

    Instructions on how to customise the palette are here: http://support.microsoft.com/kb/288412

  3. #3
    New Lounger
    Join Date
    Nov 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your answer. But what I pretend is (I don't know if this is possible): The same way I use the Data Validation for fill the cells with values from a list, it is possible to choose not values but cells fill with colors? This means that when I choose from that list one of these colors, the cell will be automaticaly formated (fill) with that color. In that way I put on the list of the Data Validation only the colors I want and I never go wrong again. If it's not possible that way there are any VBA code I can use? Thank you again.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Yes, I think you CAN do that.

    This is how I would set about it - and you might just choose to stop at the manual step !

    1. Set up a sheet with your chosen colours, and any other formats you want, in a table of cells, alongside their description.

    2. Give each of the coloured cells a meaningful range name.

    3. Now record a macro as follows:

    4. Using the Format Painter, copy the format you want and past it into your chosen target cell - that's the manual method I referred to above.

    5. Stop the recording.

    Now I don't know how good your VBA skills are, but the Macro you have just recorded contains the heart of what you want to do and it should be
    straightforward to adapt it, say, to:

    a. give you a list of formats to choose from - from the descriptions you entered in step 1

    c. apply the chosen format to the currently active cell.

    Finally if you give this VBA module a shortcut key then I think the job is done.

  5. #5
    New Lounger
    Join Date
    Nov 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your answer once again. Your suggestion is a very clever way to try to solve my problem. I gonna follow the steps you propose and then I will tell you if it results or not.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    A relatively easy approach would be to have the cell next to the colored cell which has a number 1-11 or the name of the color. You can create the validation on that cell (either by the number 1-11 or via a list of the colors), then use conditional formatting to set the color of the desired cell, based on that value or number.

    Steve
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    Nov 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That solution you propose is very simple to implemate and is not necessary use VBA code. It's a pity that Data Validation doesn't permit the use of format cells in their lists. Thank you for your help.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •