Help Changing Cell Color to Based of Date referring to a specific Interior Cell Color

[ashringg]

Happy New Year!

I am working on my yearly calendar which is on one sheet. I usually manually change the cell color to match the month and then bold Sundays (attached is my 2023 calendar) but I have been trying to get into Macros more.

Unfortunately, I cannot figure out VBA to change the cell's interior color based on the month and corresponding reference color. I don't even know where to start on how to code to bold Sundays. Can you please help?

Summary Calendar.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Jan 1	Jan 2	Jan 3	Jan 4	Jan 5	Jan 6	Jan 7	Jan 8	Jan 9	Jan 10	Jan 11	Jan 12	Jan 13	Jan 14	Jan 15	Jan 16	Jan 17	Jan 18	Jan 19	Jan 20		Year	2024
2	Jan 21	Jan 22	Jan 23	Jan 24	Jan 25	Jan 26	Jan 27	Jan 28	Jan 29	Jan 30	Jan 31	Feb 1	Feb 2	Feb 3	Feb 4	Feb 5	Feb 6	Feb 7	Feb 8	Feb 9		Month Color	R	G	B	Hex
3	Feb 10	Feb 11	Feb 12	Feb 13	Feb 14	Feb 15	Feb 16	Feb 17	Feb 18	Feb 19	Feb 20	Feb 21	Feb 22	Feb 23	Feb 24	Feb 25	Feb 26	Feb 27	Feb 28	Feb 29		Jan	255	204	255	#FFCCFF
4	Mar 1	Mar 2	Mar 3	Mar 4	Mar 5	Mar 6	Mar 7	Mar 8	Mar 9	Mar 10	Mar 11	Mar 12	Mar 13	Mar 14	Mar 15	Mar 16	Mar 17	Mar 18	Mar 19	Mar 20		Feb	204	204	255	CCCCFF
5	Mar 21	Mar 22	Mar 23	Mar 24	Mar 25	Mar 26	Mar 27	Mar 28	Mar 29	Mar 30	Mar 31	Apr 1	Apr 2	Apr 3	Apr 4	Apr 5	Apr 6	Apr 7	Apr 8	Apr 9		Mar	204	236	255	CCECFF
6	Apr 10	Apr 11	Apr 12	Apr 13	Apr 14	Apr 15	Apr 16	Apr 17	Apr 18	Apr 19	Apr 20	Apr 21	Apr 22	Apr 23	Apr 24	Apr 25	Apr 26	Apr 27	Apr 28	Apr 29		Apr	204	255	255	CCFFCC
7	Apr 30	May 1	May 2	May 3	May 4	May 5	May 6	May 7	May 8	May 9	May 10	May 11	May 12	May 13	May 14	May 15	May 16	May 17	May 18	May 19		May	204	255	204	CCFFCC
8	May 20	May 21	May 22	May 23	May 24	May 25	2024							May 26	May 27	May 28	May 29	May 30	May 31	Jun 1		Jun	204	255	253	CCFF99
9	Jun 2	Jun 3	Jun 4	Jun 5	Jun 6	Jun 7								Jun 8	Jun 9	Jun 10	Jun 11	Jun 12	Jun 13	Jun 14		Jul	255	255	204	FFFFCC
10	Jun 15	Jun 16	Jun 17	Jun 18	Jun 19	Jun 20								Jun 21	Jun 22	Jun 23	Jun 24	Jun 25	Jun 26	Jun 27		Aug	255	247	239	FFF7EF
11	Jun 28	Jun 29	Jun 30	Jul 1	Jul 2	Jul 3								Jul 4	Jul 5	Jul 6	Jul 7	Jul 8	Jul 9	Jul 10		Sep	255	204	153	FFCC99
12	Jul 11	Jul 12	Jul 13	Jul 14	Jul 15	Jul 16								Jul 17	Jul 18	Jul 19	Jul 20	Jul 21	Jul 22	Jul 23		Oct	255	204	204	FFCCCC
13	Jul 24	Jul 25	Jul 26	Jul 27	Jul 28	Jul 29	Jul 30	Jul 31	Aug 1	Aug 2	Aug 3	Aug 4	Aug 5	Aug 6	Aug 7	Aug 8	Aug 9	Aug 10	Aug 11	Aug 12		Nov	252	166	180	FCA6B4
14	Aug 13	Aug 14	Aug 15	Aug 16	Aug 17	Aug 18	Aug 19	Aug 20	Aug 21	Aug 22	Aug 23	Aug 24	Aug 25	Aug 26	Aug 27	Aug 28	Aug 29	Aug 30	Aug 31	Sep 1		Dec	233	219	233	E9DBE9
15	Sep 2	Sep 3	Sep 4	Sep 5	Sep 6	Sep 7	Sep 8	Sep 9	Sep 10	Sep 11	Sep 12	Sep 13	Sep 14	Sep 15	Sep 16	Sep 17	Sep 18	Sep 19	Sep 20	Sep 21
16	Sep 22	Sep 23	Sep 24	Sep 25	Sep 26	Sep 27	Sep 28	Sep 29	Sep 30	Oct 1	Oct 2	Oct 3	Oct 4	Oct 5	Oct 6	Oct 7	Oct 8	Oct 9	Oct 10	Oct 11
17	Oct 12	Oct 13	Oct 14	Oct 15	Oct 16	Oct 17	Oct 18	Oct 19	Oct 20	Oct 21	Oct 22	Oct 23	Oct 24	Oct 25	Oct 26	Oct 27	Oct 28	Oct 29	Oct 30	Oct 31
18	Nov 1	Nov 2	Nov 3	Nov 4	Nov 5	Nov 6	Nov 7	Nov 8	Nov 9	Nov 10	Nov 11	Nov 12	Nov 13	Nov 14	Nov 15	Nov 16	Nov 17	Nov 18	Nov 19	Nov 20
19	Nov 21	Nov 22	Nov 23	Nov 24	Nov 25	Nov 26	Nov 27	Nov 28	Nov 29	Nov 30	Dec 1	Dec 2	Dec 3	Dec 4	Dec 5	Dec 6	Dec 7	Dec 8	Dec 9	Dec 10
20	Dec 11	Dec 12	Dec 13	Dec 14	Dec 15	Dec 16	Dec 17	Dec 18	Dec 19	Dec 20	Dec 21	Dec 22	Dec 23	Dec 24	Dec 25	Dec 26	Dec 27	Dec 28	Dec 29	Dec 30
21	Dec 31
Summary Calendar

Cell Formulas
Range	Formula
A1	A1	=DATE($W$1,1,1)
B1:T7,O8:T12,B8:F20,G13:T20	B1	=A1+1
A2:A20	A2	=T1+1
G8	G8	=A1
N8:N12	N8	=F8+1
A21	A21	=IF(IF(T20+1=$A$1+365," ",T20+1)=""," ",T20+1)
B21	B21	=IF(IF(A21=" "," ",A21+1)=" ",A21+1," ")
C21:T21	C21	=IF(B21=" "," ",B21+1)

 

[SanjayGMusafir]

I usually manually change the cell color to match the month and then bold Sundays

You can try using Conditional formatting for that - 12 or lesser rules for each month and one rule for sundays, Plus if any holidays to be marked separately

 

[ashringg]

You can try using Conditional formatting for that - 12 or lesser rules for each month and one rule for sundays, Plus if any holidays to be marked separately

I dont know exactly how to apply the month

 

[SanjayGMusafir]

I dont know exactly how to apply the month

For Jan

=Month(A1)=1

 

[SanjayGMusafir]

For Jan

=Month(A1)=1

Did you try it?

Did it worked for you?

 

[ashringg]

Did you try it?

Did it worked for you?

I still cannot apply it. I go to conditonal format. Highlight cell if month(1).

 

[SanjayGMusafir]

I still cannot apply it. I go to conditonal format. Highlight cell if month(1).

Sorry I was not well (hospitalised) so couldn't revert back any earlier.

You have to use the formula I gave in #5, as it is (no modification) on the entire sheet to Conditional Format.

If you are still not able to do so. Then share the screenshot(s) of your procedure(s) to let me understand and find where the challenge is.

Also, Can you share the formula of the cell where Jan 1 is appearing in calendar.

Still, First thing First - Try the formula I gave in #5 the way I have just explained and revert. It shall work for you.

 

[ashringg]

Sorry I was not well (hospitalised) so couldn't revert back any earlier.

You have to use the formula I gave in #5, as it is (no modification) on the entire sheet to Conditional Format.

If you are still not able to do so. Then share the screenshot(s) of your procedure(s) to let me understand and find where the challenge is.

Also, Can you share the formula of the cell where Jan 1 is appearing in calendar.

Still, First thing First - Try the formula I gave in #5 the way I have just explained and revert. It shall work for you.

Oh no i hope you're feeling better! I had to figure it out but it finally worked. I couldnt figure out which type of rule.

 

[SanjayGMusafir]

Oh no i hope you're feeling better! I had to figure it out but it finally worked. I couldnt figure out which type of rule.

Glad to help you and thanks for the feedback.