Thank you. This is sort of what I was looking for. However, I want each
date/day to be independent of each other. When someone fills in F8 with a
date (and thus the day in F7), I don't want the rest of the range to be
automatically filled in. For example, I pick Oct 15 for F8, Oct 30 for G8
and Nov 4 for H8. Then F7 would automatically fill in with Wednesday, G7
would fill in with Thursday and H7 would fill in with Tuesday. Thanks!!

"Rick Rothstein" wrote:

I think this worksheet Change event code will do what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
If Target.Address = "$F$8" Then
Target.Offset(-1).Value = Format(Target.Value, "dddd")
For X = 1 To 6
Target.Offset(, X).Value = Target.Value + X
Target.Offset(-1, X).Value = Format(Target.Value + X, "dddd")
Next
End If
End Sub

The "dddd" argument in the two Format statements makes the function return
the fully spelled out day name; if you wanted the abbreviated day name, then
just change the "dddd" to "ddd".

--
Rick (MVP - Excel)


"hlock" wrote in message
...
I've figured out how to add a calendar control to my excel 2007
spreadsheet.
For example, when I click in cell F8, the calendar appears, I click on a
November 11, the date of 11/11/08 gets entered into cell F8, and the
calendar
disappears. It works great. However, what I would like to be able to set
up
is when the person clicks on November 11, the date of November gets
entered
into cell F8 AND the corresponding weekday (in this case, Tuesday) gets
automatically entered into the cell above, F7.

The calendar control is set up for a range of cells: F8:L8. I would want
the weekdays set up to be entered into the range of cells: F7:L8. Is
this
possible? Thanks.