Matching cell colors to a legend?

[Blue Max]

We want to color groups of cells (an employee shift schedule) according to a
color legend. As such, we want certain cell ranges to match the color of a
cell in a key or legend. When the legend color is changed we want the color
of that group of cells to dynamically change also. This would be somewhat
similar to linking several cell values to a single cell value, except that
the cell colors, versus the cell values, would all change to match changes
in the master cell. Is there a way to link the colors of a group of cells
to the color of a master cell in a legend?

 

[Mr. K]

Blue Max,

Just use conditional formatting of the font and background with same
colors. Shift A, Shift B and so on will have the background patterns
with same colors.
Under Excel 2003 and below there are just 3 criterias of different
shifts you can use. Under Excel 2007 more conditions are available.

 

[Blue Max]

Thank you for the reply Mr. K. It looks like conditional formatting may be
one possible option, but would you mind responding to a few additional
questions?

FIRST, it was not clear where (which dialog) or why we use the shift-A and
Shift-B options you were discussing. We are using Excel 2007 and the
pattern and color options are simply chosen from a dialog. Can you clarify
what we may have missed, it appears this may reveal a nifty shortcut?

SECOND, how can the conditional format color be linked to the actual cell
color (not a conditional format color) in the legend so that the conditional
format color will change to match the legend cell color? We want a tie-in
to the standard cell color option for simplicity sake of those changing the
colors - they will not know how to properly manage conditional formatting.
For this purpose, we need a way for all the related cells to change color
when the user simply changes the standard color in the legend cell.

THIRD, how is the conditional formatting for the destination cells linked to
the cell in the legend? Do we enter a null test of some sort so that all
the desired cells will always pass the condition for displaying the desired
color? In this case the conditional formatting does not depend on a cell
value, but simply needs to echo the standard color (not a conditional format
color) of the source (legend) cell.

Thank you for your help.

************************

 

[Mr. K]

Blue Max,

I was giving just examples of names for the shifts.. it's not a
keypress control combination of characters.
For the remaining questions, just use formatted cells or custom graphs
as you like it (use text boxes to name the legends or type in the
Excel cells). Shit A, or Team A legends as you determine the teams,
but remember, limited only to 3 conditions or teams (shifts) in Excel
2003 and below.