Need to highlight when identical text/numbers are 4 in a row

[JDinVA]

I have an excel chart that has Dates across the top and names down the side. For each ROW when the name is the same 4 days in a row (four columns) I would like to highlight that information. Example when Nancy works 4 shifts in a row I want to highlight Nancy's shifts.

Can someone help me with this code/function

I have columns starting at B and going through AF
I have rows starting at 2 and going through 20

Thank you for any help.

 

[Eric W]

Welcome to the MrExcel forum.

Try:

Book2
	A	B	C	D	E	F	G
1		7/27/2024	7/28/2024	7/29/2024	7/30/2024	7/31/2024
2		ann	ed	ann	jane	ann
3		ed	ed	ed	jane	mark
4		ann	ed	mary	mary	mark
5		ann	ed	jane	ed	ed
6		ann	ann	mary	mary	ed
7		ann	mary	ed	jane	ed
8		mark	mary	ann	mark	ed
9		mary	mary	ann	ed	ann
10		jane	mary	ann	ann	mary
11
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:AF2	Expression	=OR(COUNTIF(B2:B5,B2)=4,COUNTIF(B1:B4,B2)=4)	text	NO
B3:AF3	Expression	=OR(COUNTIF(B3:B6,B3)=4,COUNTIF(B2:B5,B3)=4,COUNTIF(B1:B4,B3)=4)	text	NO
B4:AF20	Expression	=OR(COUNTIF(B4:B7,B4)=4,COUNTIF(B3:B6,B4)=4,COUNTIF(B2:B5,B4)=4,COUNTIF(B1:B4,B4)=4)	text	NO

If you add 2 empty rows on the top of your sheet, you can get by with just 1 rule.

 

[JDinVA]

Welcome to the MrExcel forum.

Try:

Book2
	A	B	C	D	E	F	G
1		7/27/2024	7/28/2024	7/29/2024	7/30/2024	7/31/2024
2		ann	ed	ann	jane	ann
3		ed	ed	ed	jane	mark
4		ann	ed	mary	mary	mark
5		ann	ed	jane	ed	ed
6		ann	ann	mary	mary	ed
7		ann	mary	ed	jane	ed
8		mark	mary	ann	mark	ed
9		mary	mary	ann	ed	ann
10		jane	mary	ann	ann	mary
11
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:AF2	Expression	=OR(COUNTIF(B2:B5,B2)=4,COUNTIF(B1:B4,B2)=4)	text	NO
B3:AF3	Expression	=OR(COUNTIF(B3:B6,B3)=4,COUNTIF(B2:B5,B3)=4,COUNTIF(B1:B4,B3)=4)	text	NO
B4:AF20	Expression	=OR(COUNTIF(B4:B7,B4)=4,COUNTIF(B3:B6,B4)=4,COUNTIF(B2:B5,B4)=4,COUNTIF(B1:B4,B4)=4)	text	NO

If you add 2 empty rows on the top of your sheet, you can get by with just 1 rule.

Thank you but there is one slight error. The 4 in a row not 4 in a column. The people will be working different days so it needs to be the names stretching out across the row.

 

[Eric W]

OK, try:

Book2
	A	B	C	D	E	F	G
1		7/27/2024	7/28/2024	7/29/2024	7/30/2024	7/31/2024
2		ann	ed	ann	jane	ann
3		ed	ed	ed	ed	mark
4		ann	ed	mary	mary	mark
5		ann	ed	ed	ed	ed
6		ann	ann	mary	mary	ed
7		ann	mary	ed	jane	ed
8		mark	mary	ann	mark	ed
9		mary	mary	mary	mary	mary
10		jane	mary	ann	ann	mary
11
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:AF14	Expression	=OR(IFERROR(COUNTIF(OFFSET(B2,0,SEQUENCE(4,,-3),1,4),B2)=4,0))	text	NO

This requires the SEQUENCE function, available in Excel 2021 and Excel 365. If you don't have that, it can still be done, it will just be more complicated.

 

[JDinVA]

OK, try:

Book2
	A	B	C	D	E	F	G
1		7/27/2024	7/28/2024	7/29/2024	7/30/2024	7/31/2024
2		ann	ed	ann	jane	ann
3		ed	ed	ed	ed	mark
4		ann	ed	mary	mary	mark
5		ann	ed	ed	ed	ed
6		ann	ann	mary	mary	ed
7		ann	mary	ed	jane	ed
8		mark	mary	ann	mark	ed
9		mary	mary	mary	mary	mary
10		jane	mary	ann	ann	mary
11
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:AF14	Expression	=OR(IFERROR(COUNTIF(OFFSET(B2,0,SEQUENCE(4,,-3),1,4),B2)=4,0))	text	NO

This requires the SEQUENCE function, available in Excel 2021 and Excel 365. If you don't have that, it can still be done, it will just be more complicated.

thank you! I know work has what ever the new one is. I will see if I can get this to work!