Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calendar Days and Option Buttons | Excel Discussion (Misc queries) | |||
30, 60, 90 days late and due within 14 days | Excel Discussion (Misc queries) | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
Please help!! Vacation Accrual Formula | Excel Worksheet Functions | |||
Vacation Accrual Formula | Excel Worksheet Functions |