Duplicate Conditional Formatting, reset after range End

[motilulla]

Hello,

I am looking a VBA or Formula, which can highlight duplicate in the range till "End" find in the column "J", and then start again as a new range till find next "End" in column "J"

For example...
1st range would be D6:H12 highlight duplicate in colour Cyan and row 12-font colour in Red

1st range would be D13:H18 highlight duplicate in colour Yellow and row 18-font colour in Red

To do this I have use formula and adjusted ranges manually, my query is that can be done with one Formula Or VBA automatically please help

For more detail image is attached

*	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3
4
5				n1	n2	n3	n4	n5		Status	Range	Formula	Condition
6				24	34	35	42	46
7				20	27	34	46	50			Range D7:H12	Formula =--ISNUMBER(MATCH(D7,D$6:D6,0))	Fill Colour = Cyan
8				11	17	21	48	50			Range D7:H12	Formula =IF($J7="End",1,0)	End Row Font Colour = Red
9				1	16	38	42	45
10				11	17	18	39	44
11				13	15	30	35	42
12				6	15	22	37	44		End
13				3	10	23	27	44
14				6	7	38	39	44			Range D14:H18	Formula =--ISNUMBER(MATCH(D14,D$13:D13,0))	Fill Colour = Yellow
15				4	19	23	41	43			Range D14:H18	Formula =IF($J14="End",1,0)	End Row Font Colour = Red
16				3	25	27	48	49
17				20	23	26	30	33
18				4	16	21	36	44		End
19				9	16	18	19	21
20				14	17	28	29	39			Range D20:H24	Formula =--ISNUMBER(MATCH(D20,D$19:D19,0))	Fill Colour = Cyan
21				2	17	22	27	40			Range D20:H24	Formula =IF($J20="End",1,0)	End Row Font Colour = Red
22				31	32	22	40	42
23				1	3	16	24	37
24				8	18	16	35	37		End
25				2	7	24	43	46
26				10	19	23	45	49			Range D26:H32	Formula =--ISNUMBER(MATCH(D26,D$25:D25,0))	Fill Colour = Yellow
27				4	16	18	25	44			Range D26:H32	Formula =IF($J26="End",1,0)	End Row Font Colour = Red
28				1	4	7	10	50
29				11	16	24	38	40
30				13	36	40	43	50
31				3	5	34	40	49
32				13	27	24	42	43		End
33				1	32	35	45	48
34				7	8	24	27	36			Range D34:H39	Formula =--ISNUMBER(MATCH(D34,D$33:D33,0))	Fill Colour = Cyan
35				6	20	24	25	50			Range D34:H39	Formula =IF($J34="End",1,0)	End Row Font Colour = Red
36				7	8	19	28	29
37				7	17	22	27	40
38				8	13	26	34	38
39				27	28	29	34	43		End
40
41
42

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[bobsan42]

wow this is some condition:

MrExcel Playbook 01 2021-08.xlsm
	D	E	F	G	H	I	J
5	n1	n2	n3	n4	n5		Status
6	24	34	35	42	46
7	20	27	34	46	50
8	11	17	21	48	50
9	1	16	38	42	45
10	11	17	18	39	44
11	13	15	30	35	42
12	6	15	22	37	44		End
13	3	10	23	27	44
14	6	7	38	39	44
15	4	19	23	41	43
16	3	25	27	48	49
17	20	23	26	30	33
18	4	16	21	36	44		End
19	9	16	18	19	21
20	14	17	28	29	39
21	2	17	22	27	40
22	31	32	22	40	42
23	1	3	16	24	37
24	8	18	16	35	37		End
25	2	7	24	43	46
26	10	19	23	45	49
27	4	16	18	25	44
28	1	4	7	10	50
29	11	16	24	38	40
30	13	36	40	43	50
31	3	5	34	40	49
32	13	27	24	42	43		End
33	1	32	35	45	48
34	7	8	24	27	36
35	6	20	24	25	50
36	7	8	19	28	29
37	7	17	22	27	40
38	8	13	26	34	38
39	27	28	29	34	43		End
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:J39	Expression	=$J6="End"	text	NO
D6:H39	Expression	=ISNUMBER(MATCH(D6,OFFSET(D6,IFERROR(LOOKUP(2,1/($J$5:$J5="End"),ROW($J$5:$J5)),ROW($J$5))-ROW()+1,0,ROW()-IFERROR(LOOKUP(2,1/($J$5:$J5="End"),ROW($J$5:$J5)),ROW($J$5))-1,1),0))*(MOD(COUNTIF($J$5:$J5,"End"),2)=0)	text	YES
D6:H39	Expression	=ISNUMBER(MATCH(D6,OFFSET(D6,IFERROR(LOOKUP(2,1/($J$5:$J5="End"),ROW($J$5:$J5)),ROW($J$5))-ROW()+1,0,ROW()-IFERROR(LOOKUP(2,1/($J$5:$J5="End"),ROW($J$5:$J5)),ROW($J$5))-1,1),0))*(MOD(COUNTIF($J$5:$J5,"End"),2)=1)	text	NO

 

[Fluff]

@bobsan42 Xl 2000 doesn't have the iferror function.

 

[motilulla]

wow this is some condition:

bobsan42, i am very great full to you for building a huge formula, I applied as you explained but it did not worked...searching a bit I found that "IFERROR" is not compatible with my Excel 2000.

Thank you for your help

Good Luck! Have a good day

Kind Regards,
Moti

 

[bobsan42]

@bobsan42 Xl 2000 doesn't have the iferror function.

oops, my bad. i've missed this line. didn't read them all

 

[bobsan42]

bobsan42, i am very great full to you for building a huge formula, I applied as you explained but it did not worked...searching a bit I found that "IFERROR" is not compatible with my Excel 2000.

Thank you for your help

Good Luck! Have a good day

Kind Regards,
Moti

Yes, Fluff pointed my oversight. Still there is a workaround - Replace the word Status in J5 with End and try the following:

MrExcel Playbook 01 2021-08.xlsm
	D	E	F	G	H	I	J
5	n1	n2	n3	n4	n5		End
6	24	34	35	42	46
7	20	27	34	46	50
8	11	17	21	48	50
9	1	16	38	42	45
10	11	17	18	39	44
11	13	15	30	35	42
12	6	15	22	37	44		End
13	3	10	23	27	44
14	6	7	38	39	44
15	4	19	23	41	43
16	3	25	27	48	49
17	20	23	26	30	33
18	4	16	21	36	44		End
19	9	16	18	19	21
20	14	17	28	29	39
21	2	17	22	27	40
22	31	32	22	40	42
23	1	3	16	24	37
24	8	18	16	35	37		End
25	2	7	24	43	46
26	10	19	23	45	49
27	4	16	18	25	44
28	1	4	7	10	50
29	11	16	24	38	40
30	13	36	40	43	50
31	3	5	34	40	49
32	13	27	24	42	43		End
33	1	32	35	45	48
34	7	8	24	27	36
35	6	20	24	25	50
36	7	8	19	28	29
37	7	17	22	27	40
38	8	13	26	34	38
39	27	28	29	34	43		End
CF (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:J39	Expression	=$J6="End"	text	NO
D6:H39	Expression	=ISNUMBER(MATCH(D6,OFFSET(D6,LOOKUP(2,1/($J$5:$J5="End"),ROW($J$5:$J5))-ROW()+1,0,ROW()-LOOKUP(2,1/($J$5:$J5="End"),ROW($J$5:$J5))-1,1),0))*(MOD(COUNTIF($J$5:$J5,"End"),2)=1)	text	YES
D6:H39	Expression	=ISNUMBER(MATCH(D6,OFFSET(D6,LOOKUP(2,1/($J$5:$J5="End"),ROW($J$5:$J5))-ROW()+1,0,ROW()-LOOKUP(2,1/($J$5:$J5="End"),ROW($J$5:$J5))-1,1),0))*(MOD(COUNTIF($J$5:$J5,"End"),2)=0)	text	NO

 

[motilulla]

Yes, Fluff pointed my oversight. Still there is a workaround - Replace the word Status in J5 with End and try the following:

bobsan42, Wow It worked fine as treat really I appreciate your kind help and time you dedicate to find the solution I am happy, but it is problem from my side using an old version and not a very modern computer so after applying a this formula to original layout nearly 10000+ rows when I scroll my screen is flickering and slow down the worksheet.

I think may I require a VBA solution not sure that will help or not.

I am gratefully to you. Good Luck! Have a good day

Kind Regards,
Moti

 

[bobsan42]

You can use helper columns to reduce the size of the conditions and simplify them.
But still many and complex CFs may have negative effect on performance.

 

[bobsan42]

With two Additional helper columns (A and B) the performance improvement is significant on 6.5 K lines.
Try it like this:

MrExcel Playbook 01 2021-08.xlsm
	A	B	C	D	E	F	G	H	I	J
5	Group	Rows		n1	n2	n3	n4	n5		End
6	1	0		24	34	35	42	46
7	1	-1		20	27	34	46	50
8	1	-2		11	17	21	48	50
9	1	-3		1	16	38	42	45
10	1	-4		11	17	18	39	44
11	1	-5		13	15	30	35	42
12	1	-6		6	15	22	37	44		End
13	0	0		3	10	23	27	44
14	0	-1		6	7	38	39	44
15	0	-2		4	19	23	41	43
16	0	-3		3	25	27	48	49
17	0	-4		20	23	26	30	33
18	0	-5		4	16	21	36	44		End
19	1	0		9	16	18	19	21
20	1	-1		14	17	28	29	39
21	1	-2		2	17	22	27	40
22	1	-3		31	32	22	40	42
23	1	-4		1	3	16	24	37
24	1	-5		8	18	16	35	37		End
25	0	0		2	7	24	43	46
26	0	-1		10	19	23	45	49
27	0	-2		4	16	18	25	44
28	0	-3		1	4	7	10	50
29	0	-4		11	16	24	38	40
30	0	-5		13	36	40	43	50
31	0	-6		3	5	34	40	49
32	0	-7		13	27	24	42	43		End
33	1	0		1	32	35	45	48
34	1	-1		7	8	24	27	36
35	1	-2		6	20	24	25	50
36	1	-3		7	8	19	28	29
37	1	-4		7	17	22	27	40
38	1	-5		8	13	26	34	38
39	1	-6		27	28	29	34	43		End
CF (3)

Cell Formulas
Range	Formula
A6:A39	A6	=MOD(COUNTIF($J$5:$J5,"End"),2)
B6:B39	B6	=LOOKUP(2,1/($J$5:$J5="End"),ROW($J$5:$J5))-ROW()+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:J6125	Expression	=$J6="End"	text	NO
D6:J6125	Expression	=ISNUMBER(MATCH(D6,OFFSET(D6,$B6,0,-$B6,1),0))*($A6=1)	text	YES
D6:J6125	Expression	=ISNUMBER(MATCH(D6,OFFSET(D6,$B6,0,-$B6,1),0))*($A6=0)	text	NO

 

[motilulla]

With two Additional helper columns (A and B) the performance improvement is significant on 6.5 K lines.
Try it like this:

MrExcel Playbook 01 2021-08.xlsm
	A	B	C	D	E	F	G	H	I	J
5	Group	Rows		n1	n2	n3	n4	n5		End
6	1	0		24	34	35	42	46
7	1	-1		20	27	34	46	50
8	1	-2		11	17	21	48	50
9	1	-3		1	16	38	42	45
10	1	-4		11	17	18	39	44
11	1	-5		13	15	30	35	42
12	1	-6		6	15	22	37	44		End
13	0	0		3	10	23	27	44
14	0	-1		6	7	38	39	44
15	0	-2		4	19	23	41	43
16	0	-3		3	25	27	48	49
17	0	-4		20	23	26	30	33
18	0	-5		4	16	21	36	44		End
19	1	0		9	16	18	19	21
20	1	-1		14	17	28	29	39
21	1	-2		2	17	22	27	40
22	1	-3		31	32	22	40	42
23	1	-4		1	3	16	24	37
24	1	-5		8	18	16	35	37		End
25	0	0		2	7	24	43	46
26	0	-1		10	19	23	45	49
27	0	-2		4	16	18	25	44
28	0	-3		1	4	7	10	50
29	0	-4		11	16	24	38	40
30	0	-5		13	36	40	43	50
31	0	-6		3	5	34	40	49
32	0	-7		13	27	24	42	43		End
33	1	0		1	32	35	45	48
34	1	-1		7	8	24	27	36
35	1	-2		6	20	24	25	50
36	1	-3		7	8	19	28	29
37	1	-4		7	17	22	27	40
38	1	-5		8	13	26	34	38
39	1	-6		27	28	29	34	43		End
CF (3)

Cell Formulas
Range	Formula
A6:A39	A6	=MOD(COUNTIF($J$5:$J5,"End"),2)
B6:B39	B6	=LOOKUP(2,1/($J$5:$J5="End"),ROW($J$5:$J5))-ROW()+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:J6125	Expression	=$J6="End"	text	NO
D6:J6125	Expression	=ISNUMBER(MATCH(D6,OFFSET(D6,$B6,0,-$B6,1),0))*($A6=1)	text	YES
D6:J6125	Expression	=ISNUMBER(MATCH(D6,OFFSET(D6,$B6,0,-$B6,1),0))*($A6=0)	text	NO

Superb! bobsan42, yes true helper columns did the trick and improved the performance now all worked as it should.

I appreciate your kind help thank you for resolving it.

Good Luck! Have a good day

Kind Regards,
Moti