Formatting cells filled by array formulae

[Ludwig]

I have a table where the first column is a date array that fills from a selected a start date to (in effect) the current month. Hence, each month the number of elements in the array extend down the rows.

From that column I then use array formulae to "spill down" calculated values based on each row's date for a number of fields.

What I would like to have is ALL the cell formatting "spill" down for each column as well. I've set the format of the column set so that numbers, [$.c] amounts, text etc are formatted as required for the entire column, but I cannot get cell formatting for borders etc to replicate downward. Means I have to manually format the cell borders as the array rows extend each month.

PS: I don't want to see this border formatting flow down beyond the end of the array row.​

My question:

• Is there a way to get border formatting to occur for the rows with the array in it, but to *not* show below the 'table' of data.
• Or do I just have to use conditional formatting to achieve it?
  e.g. conditional formatting borders etc based on something along these lines -

• AND( row( A1 ) > row( A$5 ) , row(A1) <= count( A$5# ) + row( A$5 ) )

Thanks for the help.

 

[myall_blues]

There is no way for a formula to "change" a cell (in terms of adding borders, shading etc) so you will have to use conditional formatting or perhaps some event driven VBA code.

 

[Peter_SSs]

I have a table where the first column is a date array that fills from a selected a start date to (in effect) the current month. Hence, each month the number of elements in the array extend down the rows.

I'm not exactly sure exactly what you mean by that but hopefully this example will suffice anyway. As you had intimated and @myall_blues has confirmed, Conditional Formatting is certainly one way to go & I think you can use simpler CF than your were suggesting.

If I am right that the first column populates with dates then I would consider doing it like this.
My example spills dates from the date in A2 up to the end of the current month & spills dummy formulas down the next 3 columns
Select the first row of what will be your spilling table (A5:D5 for me and apply the CF formula =$A5<>"" and set up the appropriate borders.
With that first row still selected: Conditional formatting -> Manage Rules.. and edit the 'Applies to' range to whatever will be bigger than your data will ever be (possibly say =$A$5:$D$5000) -> Apply -> OK

Here is my result

24 11 15.xlsm
	A	B	C	D	E
1
2	25/10/2024
3
4
5	25/10/2024	odd	F	Fri
6	26/10/2024	even	S	Sat
7	27/10/2024	odd	S	Sun
8	28/10/2024	even	M	Mon
9	29/10/2024	odd	T	Tue
10	30/10/2024	even	W	Wed
11	31/10/2024	odd	T	Thu
12	1/11/2024	odd	F	Fri
13	2/11/2024	even	S	Sat
14	3/11/2024	odd	S	Sun
15	4/11/2024	even	M	Mon
16	5/11/2024	odd	T	Tue
17	6/11/2024	even	W	Wed
18	7/11/2024	odd	T	Thu
19	8/11/2024	even	F	Fri
20	9/11/2024	odd	S	Sat
21	10/11/2024	even	S	Sun
22	11/11/2024	odd	M	Mon
23	12/11/2024	even	T	Tue
24	13/11/2024	odd	W	Wed
25	14/11/2024	even	T	Thu
26	15/11/2024	odd	F	Fri
27	16/11/2024	even	S	Sat
28	17/11/2024	odd	S	Sun
29	18/11/2024	even	M	Mon
30	19/11/2024	odd	T	Tue
31	20/11/2024	even	W	Wed
32	21/11/2024	odd	T	Thu
33	22/11/2024	even	F	Fri
34	23/11/2024	odd	S	Sat
35	24/11/2024	even	S	Sun
36	25/11/2024	odd	M	Mon
37	26/11/2024	even	T	Tue
38	27/11/2024	odd	W	Wed
39	28/11/2024	even	T	Thu
40	29/11/2024	odd	F	Fri
41	30/11/2024	even	S	Sat
42
43
44
CF Spill

Cell Formulas
Range	Formula
A5:A41	A5	=SEQUENCE(EOMONTH(TODAY(),0)-A2+1,,A2)
B5:B41	B5	=IF(ISODD(DAY(A5#)),"odd","even")
C5:C41	C5	=LEFT(D5#,1)
D5:D41	D5	=TEXT(A5#,"ddd")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:D5000	Expression	=$A5<>""	text	NO

.. and if I change A2

24 11 15.xlsm
	A	B	C	D	E
1
2	15/11/2024
3
4
5	15/11/2024	odd	F	Fri
6	16/11/2024	even	S	Sat
7	17/11/2024	odd	S	Sun
8	18/11/2024	even	M	Mon
9	19/11/2024	odd	T	Tue
10	20/11/2024	even	W	Wed
11	21/11/2024	odd	T	Thu
12	22/11/2024	even	F	Fri
13	23/11/2024	odd	S	Sat
14	24/11/2024	even	S	Sun
15	25/11/2024	odd	M	Mon
16	26/11/2024	even	T	Tue
17	27/11/2024	odd	W	Wed
18	28/11/2024	even	T	Thu
19	29/11/2024	odd	F	Fri
20	30/11/2024	even	S	Sat
21
22
23
CF Spill

Cell Formulas
Range	Formula
A5:A20	A5	=SEQUENCE(EOMONTH(TODAY(),0)-A2+1,,A2)
B5:B20	B5	=IF(ISODD(DAY(A5#)),"odd","even")
C5:C20	C5	=LEFT(D5#,1)
D5:D20	D5	=TEXT(A5#,"ddd")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:D5000	Expression	=$A5<>""	text	NO

 

[Ludwig]

Thanks for the help. I'll give that approach a try.

 

[Peter_SSs]

You're welcome. Hope it works for you.

 

[Ludwig]

I'm sure it will. Coincidentally, your choice of EOMONTH & SEQUENCE to generate dates for your first column is very similar to how my first column generates an array of [eo-]months from a calculated start date.