highlight Consecutive 5 duplicate

[sohaila]

Hello

kindly help the formula
I want highlight consecutive 5 duplicated value like

a
b
b
c
c these five value hight
c
c
c
a
a

 

[kvsrinivasamurthy]

Select the range A1;A20.
Formula for CF.

=SUM(1*(IFERROR(COUNTIF(OFFSET(A1,-ROW($1:$5)+1,0,5),A1),0)=5))

 

[Peter_SSs]

Select the range A1;A20.
Formula for CF.

=SUM(1*(IFERROR(COUNTIF(OFFSET(A1,-ROW($1:$5)+1,0,5),A1),0)=5))

I don't think that is very robust. Here I have the post 2 conditional formatting in column A and an alternative in column B. Both show correct results.

24 12 05.xlsm
	A	B
1	a	a
2	b	b
3	b	b
4	c	c
5	c	c
6	c	c
7	c	c
8	c	c
9	a	a
10	a	a
11
CF2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B10	Expression	=OR(IFERROR(COUNTIF(OFFSET(B1,SEQUENCE(5,,0,-1),0,5),B1),0)=5)	text	NO
A1:A10	Expression	=SUM(1*(IFERROR(COUNTIF(OFFSET(A1,-ROW($1:$5)+1,0,5),A1),0)=5))	text	NO

Now if the user subsequently inserts any rows at the top (eg to add headings) the post 2 CF now returns incorrect results (because the row references change with the insertion) whereas the alternative in column B still highlights the correct rows.

24 12 05.xlsm
	A	B
1	Data 1	Data 2
2	a	a
3	b	b
4	b	b
5	c	c
6	c	c
7	c	c
8	c	c
9	c	c
10	a	a
11	a	a
12
CF2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B11	Expression	=OR(IFERROR(COUNTIF(OFFSET(B2,SEQUENCE(5,,0,-1),0,5),B2),0)=5)	text	NO
A2:A11	Expression	=SUM(1*(IFERROR(COUNTIF(OFFSET(A2,-ROW($2:$6)+1,0,5),A2),0)=5))	text	NO

 

[sohaila]

Thanks for your help

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.