Matching day to date

[clarkster]

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
'''''''''''''''''''''''''''''''''''''''''''''''''

 

[jim may]

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	<< Cells A1:H1 - Custom Format - "dddd"
2
3	6/1/2010	6/2/2010	6/3/2010	6/4/2010	6/5/2010	6/6/2010	6/7/2010	6/8/2010
Sheet1

Excel 2007

Cell Formulas
Range	Formula
A1	=WEEKDAY(A3)
B1	=WEEKDAY(B3)
B3	=A3+1
C1	=WEEKDAY(C3)
C3	=B3+1
D1	=WEEKDAY(D3)
D3	=C3+1
E1	=WEEKDAY(E3)
E3	=D3+1
F1	=WEEKDAY(F3)
F3	=E3+1
G1	=WEEKDAY(G3)
G3	=F3+1
H1	=WEEKDAY(H3)
H3	=G3+1

 

[Nothingman]

Could you just format the cell to only read the day of week? Just a custom format with dddd? That returns the day of week but I'm not sure if that would work for what you need.

 

[jim may]

hmmmmm,, what I did WASN'T a macro; Oh well
For what it's worth - I do it this way...
Jim

 

[clarkster]

Thanks Guys for the suggestions! I had to get a little creative. I matched the first day of the month located in c9 with the proper day by creating the following macro. I then copied and pasted it with the macro to the next 30 cells which corresponds to the correct dates. Have a great day!

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'this fills in the days of the week based off the first day of the month
[A102] = "=TEXT(WEEKDAY(c9), ""ddd"") "
Range("A102").Select
Selection.Copy
Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("C8").Select
Application.CutCopyMode = False
'copies day and autofills 31 days
Selection.AutoFill Destination:=Range("C8:AG8"), Type:=xlFillDefault
Range("C8:AG8").Select