Restrict the number of colors in Excel 2007

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote