View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Billy Liddel Billy Liddel is offline
external usenet poster
 
Posts: 527
Default Adding dates within a comment

Jock; Here is another version:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DueDate As Date, strTemp As String
Dim cmt As String

cmt = "AoS filed. Defence now due by: "
On Error Resume Next
If Intersect(Target, Me.Range("B4:B30")) Is Nothing Then
Exit Sub
Else
With Target
If .Value < "" Then
Application.EnableEvents = False
DueDate = Target.Offset(0, -1)
DueDate = DueDate + 10
strTemp = Target.Offset(0, 1).Comment.Text
Target.Offset(0, 1).ClearComments
Target.Offset(0, 1).AddComment.Text Text:=vbLf & strTemp & cmt &
DueDate
On Error GoTo 0
Application.EnableEvents = True
End If
End With
End If

End Sub

HTH
Peter

"Jock" wrote:

This first part I can get to work fine:
If a date is entered in A1, a comment will appear in C1 showing the date
(from A1) plus 10 days.
If a date is then entered in B1, the comment in C1 will be amended to the
date from B1 plus 10 days.

What I am struggling with is:
If a date is entered in A1, a comment will appear in C1 showing the date
(from A1) plus 10 days. (same as above)
However, if a date is then entered in B1, the comment in C1 will be amended
to the date from A1 plus 10 days.
partial code below:

Error Resume Next
If Not Intersect(Target, Me.Range("B4:B30")) Is Nothing Then
With Target
If .Value < "" Then
Application.EnableEvents = False
strTemp = Target.Offset(0, 1).Comment.Text
Target.Offset(0, 1).ClearComments
Target.Offset(0, 1).AddComment.Text Text:=strTemp ' & " " & "AoS
filed. Defence now due by: "
On Error GoTo 0
Application.EnableEvents = True
End If

How do I get the code to look at column A rather than the Target column B
and use the date in A?
--
Traa Dy Liooar

Jock