Conditional formatting cells up to first cell after group of consecutive zeros

[xRockox]

Hello All,

Looking for a little help with conditional formatting that meets specific criteria. I would like to highlight rows in a column based on values in an adjacent column.

For reference, I have the data highlighted for which I would like the conditional formatting to do automatically.

The criteria is as follows:
Highlight data cells in column D up to the first data point that follows the first grouping of consecutive zeros in column C.

In the sample data below, this means that I would like to highlight cells D3:D26, since C26 contains the first value that follows a grouping of consecutive zeros in cells C8:C25.

Additionally, there will be other groupings of consecutive zeros in the data (e.g. C130:C168), but I only want to highlight this first occurrence.
Data values will also vary, so there may be instances where there may be more/less/or no values before the first grouping of consecutive zeros.
There may also be instances where the quantity of consecutive zeros vary.

Thanks in advance for any help!

Sample.xlsx
	A	B	C	D
1	A	B	C	D
2	unit	unit	unit	unit
3	1	1	17.21	0.01
4	2	2	13.46	0.01
5	3	2.99	9.08	0.01
6	4	3.99	4.92	0.01
7	5	4.99	2.64	0.01
8	6	5.99	0	0.01
9	7	6.99	0	0.01
10	8	7.99	0	0.01
11	9	8.99	0	0.01
12	10	9.99	0	0.01
13	11	10.99	0	0.01
14	12	11.99	0	0.01
15	13	12.99	0	0.01
16	14	13.99	0	0.01
17	15	14.99	0	0.01
18	16	15.99	0	0.01
19	17	17	0	0.01
20	18	18	0	0.01
21	19	19	0	0.01
22	20	20	0	0.01
23	21	21	0	0.01
24	22	22	0	0.01
25	23	23	0	0.01
26	24	24	8.98	0.02
27	25	25	11.66	0.02
28	26	26	14.91	0.02
29	27	27	17.12	0.03
30	28	28	17.16	0.03
31	29	29	18.31	0.04
32	30	30	21.01	0.04
33	31	31	21.6	0.05
34	32	32	22.53	0.05
35	33	32.99	22.3	0.06
36	34	33.99	21.87	0.07
37	35	34.99	21.27	0.07
38	36	35.99	20.64	0.08
39	37	36.99	20.22	0.08
40	38	37.99	19.58	0.09
41	39	38.99	17.24	0.09
42	40	39.99	14.67	0.1
43	41	40.99	13.8	0.1
44	42	41.99	15.75	0.11
45	43	42.99	15.33	0.11
46	44	43.99	16.05	0.12
47	45	44.99	17.25	0.12
48	46	45.99	19.4	0.13
49	47	46.99	21.3	0.13
50	48	47.99	22.85	0.14
51	49	48.99	22.78	0.14
52	50	49.99	22.58	0.15
53	51	50.99	22.42	0.16
54	52	51.99	20.81	0.16
55	53	52.99	18.54	0.17
56	54	53.99	16.74	0.17
57	55	54.99	15.54	0.18
58	56	55.99	15.91	0.18
59	57	56.99	18.06	0.19
60	58	57.99	20.1	0.19
61	59	58.99	21.85	0.2
62	60	59.99	23.32	0.2
63	61	60.99	24.28	0.21
64	62	61.99	24.59	0.22
65	63	62.99	24.81	0.22
66	64	63.99	24.97	0.23
67	65	65	24.37	0.24
68	66	66	24.38	0.24
69	67	67	24.68	0.25
70	68	68	24.7	0.26
71	69	69	24.6	0.27
72	70	70	24.51	0.27
73	71	71	24.5	0.28
74	72	72	25.06	0.29
75	73	73	25.55	0.29
76	74	74	25.59	0.3
77	75	75	25.28	0.31
78	76	76	24.78	0.31
79	77	77	25.06	0.32
80	78	78	25.31	0.33
81	79	79	25.98	0.33
82	80	80	25.81	0.34
83	81	81	25.66	0.35
84	82	82	26.01	0.36
85	83	83	26.72	0.36
86	84	84	27.53	0.37
87	85	85	28.55	0.38
88	86	86	29.27	0.39
89	87	87	29.72	0.4
90	88	88	29.98	0.4
91	89	89	30.25	0.41
92	90	90	30.56	0.42
93	91	91	30.56	0.43
94	92	92	30.41	0.44
95	93	93	30.28	0.45
96	94	94	30.2	0.45
97	95	95	30.2	0.46
98	96	96	30.69	0.47
99	97	97	30.1	0.48
100	98	98	29.69	0.49
101	99	99	29.29	0.5
102	100	100	29.76	0.5
103	101	101	30.33	0.51
104	102	102	30.67	0.52
105	103	103	30.75	0.53
106	104	104	30.85	0.54
107	105	105	30.77	0.55
108	106	106	30.11	0.56
109	107	107	29.7	0.56
110	108	108	29.86	0.57
111	109	109	30.12	0.58
112	110	110	30.76	0.59
113	111	111	31.13	0.6
114	112	112	31.85	0.61
115	113	113	32.12	0.61
116	114	114	32.31	0.62
117	115	115	31.97	0.63
118	116	116	31.42	0.64
119	117	117	28.12	0.65
120	118	118	24.54	0.66
121	119	119	21.24	0.66
122	120	120	18.24	0.67
123	121	121	14.65	0.67
124	122	122	11.99	0.68
125	123	123	9.07	0.68
126	124	124	6.63	0.68
127	125	125	3	0.68
128	126	126	1.44	0.68
129	127	127	1.1	0.68
130	128	128	0	0.68
131	129	128.99	0	0.68
132	130	129.99	0	0.68
133	131	130.99	0	0.68
134	132	131.99	0	0.68
135	133	132.99	0	0.68
136	134	133.99	0	0.68
137	135	134.99	0	0.68
138	136	135.99	0	0.68
139	137	136.99	0	0.68
140	138	137.99	0	0.68
141	139	138.99	0	0.68
142	140	139.99	0	0.68
143	141	140.99	0	0.68
144	142	141.99	0	0.68
145	143	142.99	0	0.68
146	144	143.99	0	0.68
147	145	144.99	0	0.68
148	146	145.99	0	0.68
149	147	146.99	0	0.68
150	148	147.99	0	0.68
151	149	148.99	0	0.68
152	150	149.99	0	0.68
153	151	150.99	0	0.68
154	152	151.99	0	0.68
155	153	152.99	0	0.68
156	154	153.99	0	0.68
157	155	154.99	0	0.68
158	156	155.99	0	0.68
159	157	156.99	0	0.68
160	158	157.99	0	0.68
161	159	158.99	0	0.68
162	160	159.99	0	0.68
163	161	160.99	0	0.68
164	162	161.99	0	0.68
165	163	162.99	0	0.68
166	164	163.99	0	0.68
167	165	164.99	0	0.68
168	166	165.99	0	0.68
169	167	166.99	9.76	0.69
170	168	167.99	13.58	0.69
171	169	168.99	17.11	0.69
172	170	169.99	20.23	0.7
173	171	170.99	22.43	0.7
174	172	171.99	24.52	0.71
175	173	172.99	25.92	0.72
176	174	173.99	26.39	0.73
177	175	174.99	25.38	0.73
178	176	175.99	25.01	0.74
179	177	176.99	24.57	0.75
180	178	177.99	25.04	0.75
181	179	178.99	25.11	0.76
182	180	179.99	25.39	0.77
183	181	180.99	25.75	0.77
184	182	181.99	27.25	0.78
185	183	182.99	26.12	0.79
186	184	183.99	23.69	0.8
187	185	184.99	22.23	0.8
188	186	185.99	19.85	0.81
189	187	186.99	17.52	0.81
190	188	187.99	15.63	0.82
191	189	188.99	19.12	0.82
192	190	189.99	18.11	0.83
193	191	190.99	20.42	0.83
194	192	191.99	22.38	0.84
195	193	192.99	24.75	0.85
196	194	193.99	27.63	0.85
197	195	194.99	30.83	0.86
198	196	195.99	33.64	0.87
199	197	196.99	36.26	0.88
200	198	197.99	37.33	0.89
201	199	198.99	39.36	0.9
202	200	199.99	40.49	0.91
Sheet1

 

[awoohaw]

This should work on everything except your first cell. But, you may be able to augment the formula to include that:
(subsequent edit: it also gets all occurances, so maybe not the best for you. It also requires a helper column.).

Mr excel questions 63.xlsm
	A	B	C	D	E
1	unit	unit	unit	unit
2	1	1	17.21	0.01
3	2	2	13.46	0.01	FALSE
4	3	2.99	9.08	0.01	FALSE
5	4	3.99	4.92	0.01	FALSE
6	5	4.99	2.64	0.01	FALSE
7	6	5.99	0	0.01	TRUE
8	7	6.99	0	0.01	TRUE
9	8	7.99	0	0.01	TRUE
10	9	8.99	0	0.01	TRUE
11	10	9.99	46546	0.01	TRUE
12	11	10.99	313	0.01	FALSE
13	12	11.99	0	0.01	FALSE
14	13	12.99		0.01	FALSE
15	14	13.99	4654	0.01	FALSE
16	15	14.99	0	0.01	TRUE
17	16	15.99	0	0.01	TRUE
18	17	17	13	0.01	TRUE
19	18	18	0	0.01	FALSE
20	19	19	123	0.01	FALSE
21	20	20	0	0.01	TRUE
22	21	21	0	0.01	TRUE
23	22	22	0	0.01	TRUE
24	23	23	0	0.01	TRUE
25	24	24	8.98	0.02	TRUE
26	25	25	11.66	0.02	FALSE
27	26	26	14.91	0.02	FALSE
28	27	27	17.12	0.03	FALSE
29	28	28	17.16	0.03	FALSE
30	29	29	18.31	0.04	FALSE
xrockox

Cell Formulas
Range	Formula
E3:E30	E3	=OR( AND(C2:C3<>"",C2:C3=0), AND(C1:C2=0,E2=TRUE), AND(C3:C4<>"",C3:C4=0) )

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:D200	Expression	=OR( AND(C2:C3<>"",C2:C3=0), AND(C1:C2=0,E2=TRUE), AND(C3:C4<>"",C3:C4=0) )	text	NO