View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
merjet merjet is offline
external usenet poster
 
Posts: 812
Default Help needed with altering dates and date formats in VBA

Put the following in the code module for the worksheet. You said Col H
is filled in as a result of entry in another column. If so, you will
need to change the 2nd half to use the appropriate column/offsets.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 7 Then 'col G
Target.Offset(0, -6) = DateAdd("m", 1, Target)
End If
If Target.Column = 8 Then 'col H
If IsEmpty(Target.Offset(0, -1)) Then
Target.Offset(0, -7) = DateAdd("yyyy", 1, Target)
Else
Target.Offset(0, -7) = DateAdd("m", 1, Target.Offset(0, -1))
End If
End If
End Sub

Hth,
Merjet