Date issue

[Kutrenia]

Help please!

In Column A - if I input a date (other than today), then make my selection in Column F, the date that I had input in Column A does not hold. Example:

• Today is 5/4 ​
• I put 5/3 in Column A.​
• I select "Brookhaven" in Column F​
• The date in Column A changes to 5/4​
• I can go back and change Column A back to 5/3, but is there a way to make it stay 5/3 without going back?​

Current code looks like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.Range("F6:F49"), Target) Is Nothing Then
If Target = "" Then
Target.Offset(, -5).Value = ""
Target.Offset(, 2) = ""
ElseIf Target = " " Then
Target.Offset(, -5).Value = ""
Target.Offset(, 2) = ""
Else
Target.Offset(, -5).Value = Now
Target.Offset(, 2).Value2 = Now + 14
End If
End If
End Sub

​

 

[Andrew Poulsom]

This line:

Target.Offset(, -5).Value = Now

is putting today's date in column A when a cell in column F changes.

 

[Kutrenia]

Yes, and I want it to enter today's date if no other date is present. But if there is a date there, I want that date to stay and only update the Followup date which is equal to the Target.Offset(,-5) date + 14 days.

 

[Andrew Poulsom]

Try:

 With Target.Offset(, -5)
     If .Value = "" Then
        .Value = Now
     End If
End With

 

[Kutrenia]

Thank you! With a little bit of tweaking it works perfectly!
The final code I came up with is shown below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.Range("F6:F49"), Target) Is Nothing Then
With Target.Offset(, -5)
If .Value = "" Then
.Value = Now
Target.Offset(, 2) = Now + 14
Else
Target.Offset(, 2) = Target.Offset(, -5).Value + 14
End If
End With
End If
End Sub