How would I set formating to highlight overscheduled days in a row?

[rbmcc]

How do I set up formatting to highlight when a value is used x number of days in a row on a schedule?

For example I have Bob, Ron, Jan and Max working for me. My schedule looks something like this:

Mon Tue Wen Tru Fri
Bob Ron Bob Bob Bob
Jan --- Jan Max Jan
Ron --- Max Ron Max
--- --- Ron --- ---


Is there a way so that any time anyone is scheduled three days in a row they are Highlighted in yellow and if they are 4 days in a row they are highlighted red?

Also, how can this be accomplished when other formatting is included as well?
i.e. Bob is always highlighted in blue, Jan in green, Max in brown and Ron in black, so the chart would look something like this:


Mon Tue Wen Tru Fri
Bob Ron Bob Bob Bob
Jan --- Jan Max Jan
Ron --- Max Ron Max
--- --- Ron --- ---

[roadkill]

If I understand your question correctly, you should be able to do this with
some AND's and OR's in 2 levels of conditional formatting. The conditions
for each cell in a given row of 5 would all be different but, once written,
these five formats could be copied down as a group.

The first condition for each cell would need to be the one for 4 alike
(red). When either condition is met, the specified formatting (shading)
would supercede any existing shading.

As an example, to cause the first and last cell in a "chain" to turn color,
one of the conditional formatting pairs would look something like this.

condition 1 (red) for the leftmost cell (say it's cell g5)
=or(and(g5=h5,g5=i5,g5=j5),and(g5=k4,g5=j4,g5=i4))

condition 2 (yellow)
=or(and(g5=h5,g5=i5),and(g5=k4,g5=j4))

Hope this helps.

Will


"rbmcc" wrote:

>
> How do I set up formatting to highlight when a value is used x number of
> days in a row on a schedule?
>
> For example I have Bob, Ron, Jan and Max working for me. My schedule
> looks something like this:
>
> Mon Tue Wen Tru Fri
> Bob Ron Bob Bob Bob
> Jan --- Jan Max Jan
> Ron --- Max Ron Max
> --- --- Ron --- ---
>
>
> Is there a way so that any time anyone is scheduled three days in a row
> they are Highlighted in yellow and if they are 4 days in a row they are
> highlighted red?
>
> Also, how can this be accomplished when other formatting is included as
> well?
> i.e. Bob is always highlighted in blue, Jan in green, Max in brown and
> Ron in black, so the chart would look something like this:
>
>
> Mon Tue Wen Tru Fri
> Bob Ron Bob Bob Bob
> Jan --- Jan Max Jan
> Ron --- Max Ron Max
> --- --- Ron --- ---
>
>
> --
> rbmcc
> ------------------------------------------------------------------------
> rbmcc's Profile: http://www.excelforum.com/member.php...o&userid=30602
> View this thread: http://www.excelforum.com/showthread...hreadid=502547
>
>