Consecutive Count Help Needed

[FailingToExcel]

Hello, I am failing when trying to produce a formula for the desired result... I'm trying to receive an "alert"(i.e. a yellow highlighted cell with red text) if I've scheduled someone six consecutive days. The issue becomes, said person(s) can be scheduled in different time blocks that then fall in different rows, but I still need it brought to my attention that regardless of which time block they may fall in, they are still scheduled six consecutive days in a row. Example:

Monday Tuesday Wednesday Thursday Friday Saturday Sunday
1 Jan 16 2 Jan 16 3 Jan 16 4 Jan 16 5 Jan 16 6 Jan 16 7 Jan 16

12am-4am Jim Jim Jim
4am-8am Lisa Jim Jim
6am-10am Greg Jim
8am-12pm Heather
12pm-4pm George
2pm-6pm Linda
4pm-8pm Sam
8pm-12am Tom

In the example above, Jim started "Day 1" working the 12am-4am shift. He remained on that shift for three days. On Thursday he shifted to the 4am-8am shift and remained there for two days. Finally, on Saturday, which would be Jim's sixth straight work day in a row, he worked the 6am-10am shift. I need to be alerted that here, Saturday the 6th, that Jim is on his sixth consecutive work day in a row. This would also apply to any of the other workers as well, and is not limited to only Jim. Any help is greatly appreciated!! Additionally, if there is anymore information or details that I've left out but you need to complete the formula, please let me know and I will get it to you as soon as possible.

 

[FailingToExcel]

I apologize about the failed attempt at a table, here is an image of what I'd like the desired outcome to look like.

[/IMG]

 

[jtakw]

Hi,

You can use the following conditional formatting formula in your Cell M4 and O4, format fill and font color as desired, copy formatting down Columns.

Excel 2010
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
4	Jim		Jim		Jim										TRUE
5	Lisa						Jim		Jim
6	Greg										Jim
7	Heather
8	George
9	Linda
10	Sam
11	Tom
Sheet1

Cell Formulas
Range	Formula
Q4	=COUNTIF($C$4:$M$11,$C4)>=6