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

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

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

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