Color fortmatting

[daverskully]

I'll start off by giving a bit of background on the current structure. I have a worksheet table created that contains multiple date cells that will be updated by the users of the spreadsheet. On the x axis of the table are departments, while the y axis contains tasks that apply to those departments that will be completed at various dates for each deparment (Tasks i.e. send out letter, complete assignement, etc.). Thus, when a department manager completes one of these tasks, they add a date into the field which contains the date on which the task is completed. Each date field is color coated depending on the situation that applies to the task, which include:

1. (Purple) Forecasted
2. (Yellow) In Process
3. (Green) Completed
4. (Orange) On-Hold
5. (Blue) Declined

Since there are so many date fields (100 departments, 20 tasks = 2000 cells), I'm wondering what the most efficient way (for the user) to add color to these cells would be? Since each cell is a date, nothing more, there is nothing unique to use for conditional formatting. Is it possible to click on a date cell and have a small window appear right next to the cell that contains these 5 colors?.. so that when a color is selected, the background of the selected cell will be the chosen color. Or even have something set up so that the 5 colors are at the top of the spreadsheet and simply clicking on one of them will have the selected cell take on that color?

Any suggestions would be greatly appreciated!
Dave

[Richard Buttrey]

Hi,

Am I right in thinking this is a big matrix which contains only dates?. In which case the simplest solution would be to add a userform and put 5 buttons on it. Set the background property of each button to the colour you want, and in the click event of the button add the code

Please Login or Register  to view this content.

Set the Modal property of the userform to False. This will allow you to move it about and still access the sheet cells. Add a 6th button which can be hidden outside the borders of the form and set the Cancel property to True, and in the click event put the code

Please Login or Register  to view this content.

Make the form as small as possible so that just the 5 buttons show, and in a Code module, add a procedure

Please Login or Register  to view this content.

and in the Excel application make this ShowMenu macro procedure the subject of a shortcut key - say CTRL-z

Now whenever CTRL-z is pressed the userform will float above the sheet, and clicking on any of the buttons will colour the active cell. The Esc key will dismiss the form.

HTH

[daverskully]

Thanks a million! Very clear instructions and advice, this is exactly what I was looking to do.