Is it possible to lock the fill color of a cell to a specific range of colors?

I would have your colours that are acceptable in an array, and then when the worksheet is changed, check to see if the color code has changed then check this array, if its not in the array put it back to where it was.

Hi,

Personally I think you are on dangerous ground using and trying to distinguish colours with VBA in this way. I've seen two many problems to recommend this as a method.

You are making the same mistake that I see time and time again and mixing up the two elements of data capture and final reporting. The two require quite different treatments. A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

In this case a Pivot Table seems the obvious answer so capture data in a table which contains the following columns

Date
Team
Name
Shift
Value

Fields like Team, Name & Shift could usefully contain Data validation drop down (pick) lists to speed data entry.
Once you have the database then the Pivot Table can be created in a few moments and be much more flexible and efficient.

Hi,

I'd still be inclined to use the database approach for capturing the data. Then if you don't want to use a Pivot Table, (which would be a shame - time to try and educate your manager methinks, explaining how much more efficient it is and how it will save the company time and hence money), then use either standard functions to populate your table perhaps using conditional formatting, or write a simple macro to generate your table from the database.