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]

Glad it worked out.
Have a good day too. I'll try to enjoy what's left of mine