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

JDinVA

New Member
Joined
Jul 27, 2024
Messages
3
Office Version
  1. Prefer Not To Say
Platform
  1. Windows
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Welcome to the MrExcel forum.

Try:

Book2
ABCDEFG
17/27/20247/28/20247/29/20247/30/20247/31/2024
2annedannjaneann
3edededjanemark
4annedmarymarymark
5annedjaneeded
6annannmarymaryed
7annmaryedjaneed
8markmaryannmarked
9marymaryannedann
10janemaryannannmary
11
Sheet4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:AF2Expression=OR(COUNTIF(B2:B5,B2)=4,COUNTIF(B1:B4,B2)=4)textNO
B3:AF3Expression=OR(COUNTIF(B3:B6,B3)=4,COUNTIF(B2:B5,B3)=4,COUNTIF(B1:B4,B3)=4)textNO
B4:AF20Expression=OR(COUNTIF(B4:B7,B4)=4,COUNTIF(B3:B6,B4)=4,COUNTIF(B2:B5,B4)=4,COUNTIF(B1:B4,B4)=4)textNO


If you add 2 empty rows on the top of your sheet, you can get by with just 1 rule.
 
Upvote 0
Welcome to the MrExcel forum.

Try:

Book2
ABCDEFG
17/27/20247/28/20247/29/20247/30/20247/31/2024
2annedannjaneann
3edededjanemark
4annedmarymarymark
5annedjaneeded
6annannmarymaryed
7annmaryedjaneed
8markmaryannmarked
9marymaryannedann
10janemaryannannmary
11
Sheet4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:AF2Expression=OR(COUNTIF(B2:B5,B2)=4,COUNTIF(B1:B4,B2)=4)textNO
B3:AF3Expression=OR(COUNTIF(B3:B6,B3)=4,COUNTIF(B2:B5,B3)=4,COUNTIF(B1:B4,B3)=4)textNO
B4:AF20Expression=OR(COUNTIF(B4:B7,B4)=4,COUNTIF(B3:B6,B4)=4,COUNTIF(B2:B5,B4)=4,COUNTIF(B1:B4,B4)=4)textNO


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.
 
Upvote 0
OK, try:

Book2
ABCDEFG
17/27/20247/28/20247/29/20247/30/20247/31/2024
2annedannjaneann
3ededededmark
4annedmarymarymark
5annedededed
6annannmarymaryed
7annmaryedjaneed
8markmaryannmarked
9marymarymarymarymary
10janemaryannannmary
11
Sheet4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:AF14Expression=OR(IFERROR(COUNTIF(OFFSET(B2,0,SEQUENCE(4,,-3),1,4),B2)=4,0))textNO


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.
 
Upvote 0
OK, try:

Book2
ABCDEFG
17/27/20247/28/20247/29/20247/30/20247/31/2024
2annedannjaneann
3ededededmark
4annedmarymarymark
5annedededed
6annannmarymaryed
7annmaryedjaneed
8markmaryannmarked
9marymarymarymarymary
10janemaryannannmary
11
Sheet4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:AF14Expression=OR(IFERROR(COUNTIF(OFFSET(B2,0,SEQUENCE(4,,-3),1,4),B2)=4,0))textNO


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!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top