Vba to gray out weekends when dates are seperated.

[LOZ1962]

I have a year to view calendar,in a grid format 31 days accross the top & months down the a: column I would like to use VBA to grey out weekends. I have tried every formula I can find with very limited success as they do not know what year or month it is. They grey out days 6/7 in all months.. 4 days I've been trying to do this Thanks for looking & any help given. Regards Loz Baker (58 years old & still learning.)

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	2020	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17
3	January
4	February
5	March
6	April
7	May
8	June
9	July
10	August
11	September
12	October
13	November
14	December
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:R14	Expression	=WEEKDAY(DATE($A$2,MONTH($A3&1),B$2),2)>5	text	NO

 

[Joe4]

I was working on a reply, saying that you do not need VBA, you can simply use Conditional Formatting, and was getting ready to reply, when I see that Fluff had posted the same solution in the time it took me to come up with the formula. The only difference was the Conditional Formatting formula I came up with was a little different, but both should work.

Here was my formula:

=WEEKDAY(DATEVALUE($A3 & " " & B$2 & ", " & $A$2),2)>5

Just make sure that you select the range A3:BF14 before applying the Conditional Formatting, and it will apply to every cell in your range.

 

[LOZ1962]

Thank you so much guys Works a treat! another little thing can this be run in VBA coding

 

[Joe4]

can this be run in VBA coding

Conditional Formatting runs automatically, so once set up is maintenance free.

However, if you want to set up this Conditional Formatting via VBA, simply turn on your Macro Recorder while you record yourselves doing the CF steps manually.
Then stop the Recorder when finished, and you will have the VBA code you need to do this.

 

[LOZ1962]

Oh God so simple, thanks again guys, now I can get back to my proper job.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[LOZ1962]

Good afternoon

I have managed to get Bank holidays included in the Spreadsheet but cannot think how to black out non dates eg: 31st Sept & 31st November

 

[Fluff]

How about

=OR(WEEKDAY(DATE($A$2,MONTH($A3&1),B$2),2)>5,DAY(EOMONTH(DATE($A$2,MONTH($A3&1),$B$2),0))<B$2)

 

[LOZ1962]

Thanks that works great, but I'd like the Non dates blacked out and not the same color as the weekends , please.