VBA code for date & month

[sikander1986]

Hi,
I want to automate the week & month columns i.e. B & C with the help of VBA. I have date in column A & based on the date the column B & C should get updated auotmatically. Suppose the date in column A is 18-Aug-11 then column B should have 15-Aug-11(starting day of the week i.e. Monday) & column C should be Aug'11. For your reference below is the example of screenshot.
A B C
DATE WEEK MONTH
27-Jul-11 25-Jul-11 Jul'11
11-Aug-11 8-Aug-11 AUG'11
17-Aug-11 15-Aug-11 AUG'11
18-Aug-11 15-Aug-11 AUG'11
19-Aug-11 15-Aug-11 AUG'11

Thanks for your help in advance.

 

[sikander1986]

now the value is getting updated as & when i am manually typing the date. However, it is not updating the Column B & C if i am pasting or doing pastespecial/values with more dates. In this case i have to press F2 key & press enter to get update Column B & C. But if i remove date the Column B & C is remaining as it is...

 

[mikerickson]

If you are dealing with pasting more than one entry, you have to loop through Target

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oneCell as Range
    On Error Goto ErrorOut
    With Target
        If .Column = 1 Then
            Application.EnableEvents = False
            For Each oneCell in .Columns(1)
                With oneCell 
                    If IsDate(.Value) Then
                        .Offset(0, 1).FormulaR1C1 = "=RC[-1]-(WEEKDAY(RC[-1],3))                .Offset(0, 2).FormulaR1C1 = "=TEXT(RC[-2],""mmm""""'""""yy"")"
                        .Offset(0, 1).Resize(1, 2).Value = .Offset(0, 1).Resize(1, 2).Value
                    End If
                End With
            Next oneCell
        End If
    End With
ErrorOut:
    Application.EnableEvents = True
End Sub