I would like to write a macro that will match the date to the day (see example below) So if it is June 1st for example it would be Tuesday.
<TABLE style="WIDTH: 616pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=825 border=0 x:str><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=11 width=75><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=75 height=17>Tuesday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Wednesday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Thursday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Friday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Saturday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Sunday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Monday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Tuesday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Wednesday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Thursday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Friday</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 896pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1200 border=0 x:str><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=16 width=75><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right width=75 height=17 x:num="40330">6/1/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40331">6/2/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40332">6/3/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40333">6/4/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40334">6/5/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40335">6/6/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40336">6/7/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40337">6/8/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40338">6/9/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40339">6/10/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40340">6/11/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40341">6/12/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40342">6/13/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40343">6/14/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40344">6/15/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40345">6/16/2010</TD></TR></TBODY></TABLE>
This was an attempt to do it based off a script found on the internet and modified
'obtaining days of the week within each month
Dim givenDate As Date
Dim modifiedDate As Date
Dim dayNumber As Integer
Dim dayOfWeek As String
givenDate = CDate(Range("c9").Value)
modifiedDate = DateAdd("d", 2, givenDate)
dayNumber = Weekday(modifiedDate)
Select Case dayNumber
Case 1
dayOfWeek = "Monday"
Case 2
dayOfWeek = "Tuesday"
Case 3
dayOfWeek = "Wednesday"
Case 4
dayOfWeek = "Thursday"
Case 5
dayOfWeek = "Friday"
Case 6
dayOfWeek = "Saturday"
Case 7
dayOfWeek = "Sunday"
End Select
Range("C8").Value = dayOfWeek
Range("C8:Ag8").Font.Size = 10
Range("C8:Ag8").Font.Name = "Arial"
Range("C8").Select
Selection.AutoFill Destination:=Range("C8:AG8"), Type:=xlFillDefault
Range("C8:AG8").Select
'''''''''''''''''''''''''''''''''''''''''''''''''
<TABLE style="WIDTH: 616pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=825 border=0 x:str><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=11 width=75><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=75 height=17>Tuesday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Wednesday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Thursday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Friday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Saturday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Sunday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Monday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Tuesday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Wednesday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Thursday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Friday</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 896pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1200 border=0 x:str><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=16 width=75><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right width=75 height=17 x:num="40330">6/1/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40331">6/2/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40332">6/3/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40333">6/4/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40334">6/5/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40335">6/6/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40336">6/7/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40337">6/8/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40338">6/9/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40339">6/10/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40340">6/11/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40341">6/12/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40342">6/13/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40343">6/14/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40344">6/15/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40345">6/16/2010</TD></TR></TBODY></TABLE>
This was an attempt to do it based off a script found on the internet and modified
'obtaining days of the week within each month
Dim givenDate As Date
Dim modifiedDate As Date
Dim dayNumber As Integer
Dim dayOfWeek As String
givenDate = CDate(Range("c9").Value)
modifiedDate = DateAdd("d", 2, givenDate)
dayNumber = Weekday(modifiedDate)
Select Case dayNumber
Case 1
dayOfWeek = "Monday"
Case 2
dayOfWeek = "Tuesday"
Case 3
dayOfWeek = "Wednesday"
Case 4
dayOfWeek = "Thursday"
Case 5
dayOfWeek = "Friday"
Case 6
dayOfWeek = "Saturday"
Case 7
dayOfWeek = "Sunday"
End Select
Range("C8").Value = dayOfWeek
Range("C8:Ag8").Font.Size = 10
Range("C8:Ag8").Font.Name = "Arial"
Range("C8").Select
Selection.AutoFill Destination:=Range("C8:AG8"), Type:=xlFillDefault
Range("C8:AG8").Select
'''''''''''''''''''''''''''''''''''''''''''''''''
Last edited: