Action A Macro on enter in a worksheet

[J Scene]

Hi

I have a list of dates for product to be dispatched in column B and I'm trying to get the date beside in column C for the start of production (10 days before dispatch date). This works fine while I am using Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) but I want to action it on enter, So when I change it to Worksheet_Change(ByVal Target As Range) the Cancel = True will no longer work as it is not defined. Can anyone please help with this I dont no a lot about VBA I have created this from code I have found on the net.

'Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
Dim rng As Range
Dim d1 As Date
Dim d2 As Date
Dim R1 As Range
Dim wf As WorksheetFunction
Dim lastRow As Long
lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row + 1
Set rng = Range("B3:B" & lastRow - 1)
Set wf = Application.WorksheetFunction






If Not Intersect(Target, rng) Is Nothing Then
Application.EnableEvents = False


Cancel = True 'stop the edit mode

d1 = Range(Target.Address)
d2 = wf.WorkDay(d1, -10)
Set R1 = Range(Target.Address)
R1.Offset(0, 1).Value = d2
End If
Application.EnableEvents = True
End Sub

Thanks

 

[JoeMo]

Untested, but this should get you started:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim d1 As Date
Dim d2 As Date
Dim wf As WorksheetFunction
Dim lastRow As Long
lastRow = Me.Cells(Rows.Count, "B").End(xlUp).Row + 1
Set rng = Range("B3:B" & lastRow - 1)
Set wf = Application.WorksheetFunction
Set Target = Target(1)
If Not Intersect(Target, rng) Is Nothing And IsDate(Target.Value) Then
    Application.EnableEvents = False
    d1 = Target.Value
    d2 = wf.WorkDay(d1, -10)
    Target.Offset(0, 1).Value = d2
End If
Application.EnableEvents = True
End Sub

 

[J Scene]

Thanks Joe this is exactly what I need. I have been trying to do it all day.