If formula and Indirect formula

[Ev1lZer0]

I am trying to get color bands on similar dates while having alternating colors on the rest of the table. I started by using an IF formula and had "=IF(A1=A2,B1,NOT(B1))" I also had come conditional formatting involved.
If you need more information, I am using the process shown on the website. "Colour Bands in Excel Table Based on Dates – Contextures Blog"

It works really well, except for when I insert or delete rows. Which will happen frequently.

When I add rows, I get an inconsistent formula error; when I delete rows I get a #REF error. These errors always start BELOW the inserted row.

---

To resolve this, I tried using an INDIRECT formula with the IF formula and ended up with this. =IF((ADDRESS(ROW()-1,COLUMN()-1))=(ADDRESS(ROW(),COLUMN()-1)),B9,NOT(B9)) it does not work. It is now alternating my true false statement incorrectly. in addition, the [value if false] and [value if true] statement is still referencing the cell above which ends up breaking the formula in the same way I stated earlier.
When I add rows, I get an inconsistent formula error; when I delete rows I get a #REF error. These errors always start BELOW the inserted row.

---

lastly, I tried changing the formula to "=IF((ADDRESS(ROW()-1,COLUMN()-1))=(ADDRESS(ROW(),COLUMN()-1)),(ADDRESS(ROW()-1,COLUMN())),NOT(ADDRESS(ROW()-1,COLUMN())))" My hope was to resolve the #REF error and Inconsistent formula error and address the issue where this formula seems to not correctly label true false.

--

I'm pulling out my hair and desperately need help

The end goal... to alternate color bands based on similar dates like the image below. The true and false should be alternated while similar dates should be grouped together and alternated.

And to state again, the solution needs to work when adding or deleting cells - mine in the past have not.

 

[Ev1lZer0]

I deleted the TRUE/FALSE column since it was just a helper column anyway.

Another option without any helper columns

Fluff.xlsm
	A
1	Date of Trial
2	09/06/2022
3	09/06/2022
4	09/12/2022
5	09/12/2022
6	09/12/2022
7	09/12/2022
8	09/12/2022
9	09/12/2022
10	09/12/2022
11	09/12/2022
12	09/12/2022
13	09/12/2022
14	09/12/2022
15	09/12/2022
16	09/12/2022
17	09/12/2022
18	19/09/2022
19	19/09/2022
20	20/09/2022
21	21/09/2022
22	22/09/2022
23	23/09/2022
24	26/09/2022
25	26/09/2022
26	26/09/2022
27	26/09/2022
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A27	Expression	=MOD(SUMPRODUCT(--($A$2:$A2<>$A$1:$A1)),2)=0	text	NO

I deleted the TRUE/FALSE column since it was a helper column to begin with and your CF rule did not interact with it. I then used your CF rule and it works. . . until it doesn't.

deleting rows works fine, but when adding new rows, it breaks the CF.

Here is a minisheet that shows what happens when I add a row.
In the example, I set the conditional formatting to highlight in orange just for now and I've deleted the contents of column b for security reasons. Additionally, I have a pink, yellow, and red formatting that is working just fine and is not part of the issue. Lastly, the alternating gray-white color band is what I want for column b and everything after. With that said, the gray-white color band in column a should just be white, while the orange will be gray when I'm done.

2022 Color Coded Watch List 1.xlsx
	A	B
1	Date of Trial	Case Number
2	09/06/22
3	09/06/22
4	09/12/22
5	09/12/22
6	09/12/22
7	09/12/22
8	09/12/22
9	09/12/22
10	09/12/22
11	09/12/22
12	09/12/22
13	09/12/22
14	09/12/22
15	09/12/22
16	09/12/22
17	09/12/22
18	09/19/22
19	09/19/22
20	09/20/22
21	09/21/22
22	09/22/22
23	09/23/22
24	09/26/22
25	09/26/22
26	09/26/22
27	09/26/22
28	09/26/22
29	09/26/22
30	09/26/22
31	09/26/22
32	10/03/22
33	10/03/22
34	10/03/22
35	10/03/22
36	10/03/22
37	10/03/22
38	10/03/22
39	10/10/22
40	10/10/22
41	10/10/22
42	10/10/22
43	10/10/22
44	10/10/22
45	10/10/22
46	10/10/22
47	10/17/22
48	10/17/22
49	10/17/22
50	10/17/22
51	10/17/22
52	10/17/22
53	10/17/22
54	10/17/22
55	10/24/22
56	10/24/22
57	10/24/22
58	10/24/22
59	10/24/22
60	10/24/22
61	10/25/22
62	10/31/22
63	10/31/22
64	10/31/22
65	10/31/22
66	10/31/22
67	10/31/22
68	10/31/22
69	11/07/22
70	11/07/22
71	11/07/22
72	11/07/22
73	11/07/22
74	11/07/22
75	11/07/22
76	11/07/22
77	11/07/22
78	11/14/22
79	11/14/22
80	11/14/22
81	11/14/22
82	11/14/22
83	11/14/22
84	11/14/22
85	11/14/22
86	11/14/22
87	11/14/22
88	11/14/22
89	11/14/22
90	11/21/22
91	11/21/22
92	11/21/22
93	11/28/22
94	11/28/22
95	11/28/22
96	12/12/22
97	12/12/22
98	12/12/22
99	12/12/22
100	12/12/22
101	12/19/22
102	01/09/23
103	01/09/23
104	01/17/23
105	01/17/23
106	01/17/23
107	01/23/23
108	01/23/23
109	01/23/23
110	01/30/23
111	01/30/23
112	02/06/23
113	02/06/23
114	02/06/23
115	02/13/23
116	02/13/23
117	02/13/23
118	02/13/23
119	02/21/23
120	02/27/23
121	02/27/23
122	02/27/23
2022 Trial Calendar

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A122	Expression	=MOD(SUMPRODUCT(--($A$2:$A2<>$A$1:$A1)),2)=0	text	NO
B1:M2,B3:C3,J3:M3,E3:H3,B4:M1048576	Expression	=#REF!="Pink"	text	NO
B1:M2,B3:C3,J3:M3,E3:H3,B4:M1048576	Expression	=#REF!="Yellow"	text	NO
B1:M2,B3:C3,J3:M3,E3:H3,B4:M1048576	Expression	=#REF!="Red"	text	NO

 

[Fluff]

In what way is it "breaking" the CF, the data you've posted looks fine.

 

[Ev1lZer0]

I deleted the TRUE/FALSE column since it was just a helper column anyway.

I deleted the TRUE/FALSE column since it was a helper column to begin with and your CF rule did not interact with it. I then used your CF rule and it works. . . until it doesn't.

deleting rows works fine, but when adding new rows, it breaks the CF.

Here is a minisheet that shows what happens when I add a row.
In the example, I set the conditional formatting to highlight in orange just for now and I've deleted the contents of column b for security reasons. Additionally, I have a pink, yellow, and red formatting that is working just fine and is not part of the issue. Lastly, the alternating gray-white color band is what I want for column b and everything after. With that said, the gray-white color band in column a should just be white, while the orange will be gray when I'm done.

2022 Color Coded Watch List 1.xlsx
	A	B
1	Date of Trial	Case Number
2	09/06/22
3	09/06/22
4	09/12/22
5	09/12/22
6	09/12/22
7	09/12/22
8	09/12/22
9	09/12/22
10	09/12/22
11	09/12/22
12	09/12/22
13	09/12/22
14	09/12/22
15	09/12/22
16	09/12/22
17	09/12/22
18	09/19/22
19	09/19/22
20	09/20/22
21	09/21/22
22	09/22/22
23	09/23/22
24	09/26/22
25	09/26/22
26	09/26/22
27	09/26/22
28	09/26/22
29	09/26/22
30	09/26/22
31	09/26/22
32	10/03/22
33	10/03/22
34	10/03/22
35	10/03/22
36	10/03/22
37	10/03/22
38	10/03/22
39	10/10/22
40	10/10/22
41	10/10/22
42	10/10/22
43	10/10/22
44	10/10/22
45	10/10/22
46	10/10/22
47	10/17/22
48	10/17/22
49	10/17/22
50	10/17/22
51	10/17/22
52	10/17/22
53	10/17/22
54	10/17/22
55	10/24/22
56	10/24/22
57	10/24/22
58	10/24/22
59	10/24/22
60	10/24/22
61	10/25/22
62	10/31/22
63	10/31/22
64	10/31/22
65	10/31/22
66	10/31/22
67	10/31/22
68	10/31/22
69	11/07/22
70	11/07/22
71	11/07/22
72	11/07/22
73	11/07/22
74	11/07/22
75	11/07/22
76	11/07/22
77	11/07/22
78	11/14/22
79	11/14/22
80	11/14/22
81	11/14/22
82	11/14/22
83	11/14/22
84	11/14/22
85	11/14/22
86	11/14/22
87	11/14/22
88	11/14/22
89	11/14/22
90	11/21/22
91	11/21/22
92	11/21/22
93	11/28/22
94	11/28/22
95	11/28/22
96	12/12/22
97	12/12/22
98	12/12/22
99	12/12/22
100	12/12/22
101	12/19/22
102	01/09/23
103	01/09/23
104	01/17/23
105	01/17/23
106	01/17/23
107	01/23/23
108	01/23/23
109	01/23/23
110	01/30/23
111	01/30/23
112	02/06/23
113	02/06/23
114	02/06/23
115	02/13/23
116	02/13/23
117	02/13/23
118	02/13/23
119	02/21/23
120	02/27/23
121	02/27/23
122	02/27/23
2022 Trial Calendar

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A122	Expression	=MOD(SUMPRODUCT(--($A$2:$A2<>$A$1:$A1)),2)=0	text	NO
B1:M2,B3:C3,J3:M3,E3:H3,B4:M1048576	Expression	=#REF!="Pink"	text	NO
B1:M2,B3:C3,J3:M3,E3:H3,B4:M1048576	Expression	=#REF!="Yellow"	text	NO
B1:M2,B3:C3,J3:M3,E3:H3,B4:M1048576	Expression	=#REF!="Red"	text	NO

In what way is it "breaking" the CF, the data you've posted looks fine.

I copied the wrong info.
See below for the correction.

I'm sorry, the example I included is working fine. Here is the example after I add two Rows. To be clear, rows 4 & 19 are new. You can see the changes it made to the CF. The "applies to" section has been drastically changed, and it added a new CF rule.

2022 Color Coded Watch List 1.xlsx
	A	B
1	Date of Trial	Case Number
2	09/06/22
3	09/06/22
4	09/11/22
5	09/12/22
6	09/12/22
7	09/12/22
8	09/12/22
9	09/12/22
10	09/12/22
11	09/12/22
12	09/12/22
13	09/12/22
14	09/12/22
15	09/12/22
16	09/12/22
17	09/12/22
18	09/12/22
19	09/12/22
20	09/19/22
21	09/19/22
22	09/20/22
23	09/21/22
24	09/22/22
25	09/23/22
26	09/26/22
27	09/26/22
28	09/26/22
29	09/26/22
30	09/26/22
31	09/26/22
32	09/26/22
33	09/26/22
34	10/03/22
35	10/03/22
36	10/03/22
37	10/03/22
38	10/03/22
39	10/03/22
40	10/03/22
41	10/10/22
42	10/10/22
43	10/10/22
44	10/10/22
45	10/10/22
46	10/10/22
47	10/10/22
48	10/10/22
49	10/17/22
50	10/17/22
51	10/17/22
52	10/17/22
53	10/17/22
54	10/17/22
55	10/17/22
56	10/17/22
57	10/24/22
58	10/24/22
59	10/24/22
60	10/24/22
61	10/24/22
62	10/24/22
63	10/25/22
64	10/31/22
65	10/31/22
66	10/31/22
67	10/31/22
68	10/31/22
69	10/31/22
70	10/31/22
71	11/07/22
72	11/07/22
73	11/07/22
74	11/07/22
75	11/07/22
76	11/07/22
77	11/07/22
78	11/07/22
79	11/07/22
80	11/14/22
81	11/14/22
82	11/14/22
83	11/14/22
84	11/14/22
85	11/14/22
86	11/14/22
87	11/14/22
88	11/14/22
89	11/14/22
90	11/14/22
91	11/14/22
92	11/21/22
93	11/21/22
94	11/21/22
95	11/28/22
96	11/28/22
97	11/28/22
98	12/12/22
99	12/12/22
100	12/12/22
101	12/12/22
102	12/12/22
103	12/19/22
104	01/09/23
105	01/09/23
106	01/17/23
107	01/17/23
108	01/17/23
109	01/23/23
110	01/23/23
111	01/23/23
112	01/30/23
113	01/30/23
114	02/06/23
115	02/06/23
116	02/06/23
117	02/13/23
118	02/13/23
119	02/13/23
120	02/13/23
121	02/21/23
122	02/27/23
123	02/27/23
124	02/27/23
2022 Trial Calendar

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A4,A6:A19,A21:A124	Expression	=MOD(SUMPRODUCT(--($A$2:$A2<>$A$1:$A1)),2)=0	text	NO
B1:M2,B3:C4,J3:M4,E3:H4,B5:M1048576	Expression	=#REF!="Pink"	text	NO
B1:M2,B3:C4,J3:M4,E3:H4,B5:M1048576	Expression	=#REF!="Yellow"	text	NO
B1:M2,B3:C4,J3:M4,E3:H4,B5:M1048576	Expression	=#REF!="Red"	text	NO
A5,A20	Expression	=MOD(SUMPRODUCT(--($A$2:$A5<>$A$1:$A3)),2)=0	text	NO

 

[Fluff]

Ok, how about

=ISEVEN(MATCH($A2,UNIQUE($A$2:$A$124),0))

 

[Ev1lZer0]

It worked!

Ok, how about

=ISEVEN(MATCH($A2,UNIQUE($A$2:$A$124),0))

Part 2 of the issue, which is more minor, but still important.
The alternating color as you see in A32-A38 I just want it to be blue (again temporary color for now).
So that way you would end up with alternating color bands of orange and blue.

This screenshot has other CF which are causing some of the red cells.

Desired outcome

 

[Fluff]

For the blue you can use the same formula just change iseven to isodd.

 

[Ev1lZer0]

This is wonderful!

I have spent hours on this and you came swooping in with an alternative formula! Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Ev1lZer0]

With the CF not holding while I'm filtered in the table, I'm confident this will be fine. But if I wanted to make the CF hold true even while filtering, can this be done with this formula and a VBA that does something?

I briefly looked into @cmowla's formula, but it got too complicated and I couldn't figure out what exactly wasn't working.

 

[Fluff]

With a helper column like

Fluff.xlsm
	A	B	C
1	Date of Trial	Column1	Column2
2	09/06/2022	1
3	09/06/2022	1
5	10/09/2022	1
7	12/09/2022	1
8	12/09/2022	1
9	12/09/2022	1
10	12/09/2022	1
11	12/09/2022	1
12	12/09/2022	1
13	12/09/2022	1
14	12/09/2022	1
15	12/09/2022	1
16	12/09/2022	1
17	12/09/2022	1
18	12/09/2022	1
19	12/09/2022	1
20	12/09/2022	1
21	13/09/2022	1
22	19/09/2022	1
23	19/09/2022	1
24	20/09/2022	1
25	21/09/2022	1
26	26/09/2022	1
27	26/09/2022	1
28	26/09/2022	1
29	26/09/2022	1
30	28/09/2022	1
31	30/09/2022	1
32	01/10/2022	1
33	02/10/2022	1
34	03/10/2022	1
Main

Cell Formulas
Range	Formula
B2:B3,B5,B7:B34	B2	=SUBTOTAL(103,[@[Date of Trial]])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C34	Expression	=ISODD(MATCH($A2,UNIQUE(FILTER($A$2:$A$34,$B$2:$B$34=1)),0))	text	NO
A2:C34	Expression	=ISEVEN(MATCH($A2,UNIQUE(FILTER($A$2:$A$34,$B$2:$B$34=1)),0))	text	NO