Copy conditional formatting with $ in the mix???

[Geek Girl 007]

Book1.xlsx
	A	B	C	D	E	F
1	Day	Date	AM	LUNCH	PM	ED
2			Name 1	Name 1	Name 1	Name 1
3	Sunday	03/03/2025	Name 2	0	Name 2	0
4			Name 3	0	Name 3	0
5
6	Monday	04/03/2025
7
8
9	Tuesday	05/03/2025
10
11
12	Wednesday	06/03/2025
13
14
15	Thursday	07/03/2025
16
17
18	Friday	08/03/2025
19
20
21	Saturday	09/03/2025
22
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F4	Expression	=COUNTA($C$2:$F$4)=12	text	NO

A very helpful person solved 'Change colour of block cells if other cells are full?,
I wanted a block of cells to change colour if a certain number of cells were full, however I didn't state that I needed to copy the formula to all DAYS, in blocks...... does this make sense??? will I need a different formula???

 

[Jeffrey Mahoney]

=COUNTA($C2:$F4)=12

Try just changing the range that is affected in the CF from A2:F4 to A2:F22

 

[DanteAmor]

Try 3 conditional formats.

Here the formulas for the entire range:

varios 06feb2025.xlsm
	A	B	C	D	E	F
1	Day	Date	AM	LUNCH	PM	ED
2			Name 1	Name 1	Name 1	Name 1
3	Sunday	03/03/2025	Name 2	0	Name 2	0
4			Name 3	0	Name 3	0
5			a	a	a	a
6	Monday	04/03/2025	B	B	B
7			B	B	B
8			1	2	3	4
9	Tuesday	05/03/2025	1	2	3	4
10			1	2	3	4
11
12	Wednesday	06/03/2025
13
14			A	B	C	D
15	Thursday	07/03/2025	A	B	C	D
16			A	B	C	D
17
18	Friday	08/03/2025
19
20
21	Saturday	09/03/2025
Hoja2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F22	Expression	=AND(COUNTA(OFFSET($C2,0,0,3,4))=12,MOD(ROW(),3)=2)	text	NO
A2:F22	Expression	=AND(COUNTA(OFFSET($C2,-1,0,3,4))=12,MOD(ROW(),3)=0)	text	NO
A2:F22	Expression	=AND(COUNTA(OFFSET($C2,-2,0,3,4))=12,MOD(ROW(),3)=1)	text	NO

 

[Geek Girl 007]

Try 3 conditional formats.

Here the formulas for the entire range:

varios 06feb2025.xlsm
	A	B	C	D	E	F
1	Day	Date	AM	LUNCH	PM	ED
2			Name 1	Name 1	Name 1	Name 1
3	Sunday	03/03/2025	Name 2	0	Name 2	0
4			Name 3	0	Name 3	0
5			a	a	a	a
6	Monday	04/03/2025	B	B	B
7			B	B	B
8			1	2	3	4
9	Tuesday	05/03/2025	1	2	3	4
10			1	2	3	4
11
12	Wednesday	06/03/2025
13
14			A	B	C	D
15	Thursday	07/03/2025	A	B	C	D
16			A	B	C	D
17
18	Friday	08/03/2025
19
20
21	Saturday	09/03/2025
Hoja2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F22	Expression	=AND(COUNTA(OFFSET($C2,0,0,3,4))=12,MOD(ROW(),3)=2)	text	NO
A2:F22	Expression	=AND(COUNTA(OFFSET($C2,-1,0,3,4))=12,MOD(ROW(),3)=0)	text	NO
A2:F22	Expression	=AND(COUNTA(OFFSET($C2,-2,0,3,4))=12,MOD(ROW(),3)=1)	text	NO

Hi there,
This works, but I wanted to add another row (so there are sets of 4 instead of 3, I tried to write a new code but couldn't.
Can you please help???
I will mark this as a soloution also. Thank you

 

[DanteAmor]

This works, but I wanted to add another row (so there are sets of 4 instead of 3

Try:

DANTE AMOR
	A	B	C	D	E	F
1	Day	Date	AM	LUNCH	PM	ED
2			Name 1	1	1	1
3	Sunday	03/03/2025	Name 2	1	1	1
4			Name 3	1	1	1
5			Name 4	1	1	1
6			Name 1	2		2
7	Monday	04/03/2025	Name 2	2	2	2
8			Name 3	2	2	2
9			Name 4	2	2	2
10			Name 1	3	3	3
11	Tuesday	05/03/2025	Name 2	3	3	3
12			Name 3	3	3	3
13			Name 4	3	3	3
14			Name 1		4	4
15	Wednesday	06/03/2025	Name 2	4		4
16			Name 3	4	4
17			Name 4	4	4
18			Name 1	5	5	5
19	Thursday	07/03/2025	Name 2	5	5	5
20			Name 3	5	5	5
21			Name 4	5	5	5
22			Name 1
23	Friday	08/03/2025	Name 2
24			Name 3
25			Name 4
26			Name 1	7	7	7
27	Saturday	09/03/2025	Name 2	7	7	7
28			Name 3	7	7	7
29			Name 4	7	7	7
Hoja2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F29	Expression	=AND(COUNTA(OFFSET($C2,0,0,4,4))=16,MOD(ROW(),4)=2)	text	NO
A2:F29	Expression	=AND(COUNTA(OFFSET($C2,-1,0,4,4))=16,MOD(ROW(),4)=3)	text	NO
A2:F29	Expression	=AND(COUNTA(OFFSET($C2,-2,0,4,4))=16,MOD(ROW(),4)=0)	text	NO
A2:F29	Expression	=AND(COUNTA(OFFSET($C2,-3,0,4,4))=16,MOD(ROW(),4)=1)	text	NO

 

[Geek Girl 007]

Try:

DANTE AMOR
	A	B	C	D	E	F
1	Day	Date	AM	LUNCH	PM	ED
2			Name 1	1	1	1
3	Sunday	03/03/2025	Name 2	1	1	1
4			Name 3	1	1	1
5			Name 4	1	1	1
6			Name 1	2		2
7	Monday	04/03/2025	Name 2	2	2	2
8			Name 3	2	2	2
9			Name 4	2	2	2
10			Name 1	3	3	3
11	Tuesday	05/03/2025	Name 2	3	3	3
12			Name 3	3	3	3
13			Name 4	3	3	3
14			Name 1		4	4
15	Wednesday	06/03/2025	Name 2	4		4
16			Name 3	4	4
17			Name 4	4	4
18			Name 1	5	5	5
19	Thursday	07/03/2025	Name 2	5	5	5
20			Name 3	5	5	5
21			Name 4	5	5	5
22			Name 1
23	Friday	08/03/2025	Name 2
24			Name 3
25			Name 4
26			Name 1	7	7	7
27	Saturday	09/03/2025	Name 2	7	7	7
28			Name 3	7	7	7
29			Name 4	7	7	7
Hoja2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F29	Expression	=AND(COUNTA(OFFSET($C2,0,0,4,4))=16,MOD(ROW(),4)=2)	text	NO
A2:F29	Expression	=AND(COUNTA(OFFSET($C2,-1,0,4,4))=16,MOD(ROW(),4)=3)	text	NO
A2:F29	Expression	=AND(COUNTA(OFFSET($C2,-2,0,4,4))=16,MOD(ROW(),4)=0)	text	NO
A2:F29	Expression	=AND(COUNTA(OFFSET($C2,-3,0,4,4))=16,MOD(ROW(),4)=1)	text	NO

Thank you so so much, it makes sense now, I just couldn't work it out