Automaticaly Enter Date in selected Cell

[Guest]

All,

I am trying to perfect a macro that will, when the word "Reporting" is put
in cell D2 enter a date stamp into cell I2.

I have got this far after some experimenting but it keeps coming up with an
error message. The macro I have so far is,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("D2100"), .Cells) Is Nothing Then
Application.EnableEvents = False
If Target = "Reporting" Then
With .Offset(0, 5)
.NumberFormat = "dd/mm/yyyy"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub

Any Suggestions?

 

[JE McGimpsey]

You were missing an End If. Try

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("D2100"), .Cells) Is Nothing Then
If Target = "Reporting" Then
On Error Resume Next
Application.EnableEvents = False
With .Offset(0, 5)
.NumberFormat = "dd/mm/yyyy"
.Value = Now
End With
Application.EnableEvents = True
End If
End If
End With
End Sub

 

[Guest]

Worked perfectly thanks a lot!!!

You were missing an End If. Try

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("D2100"), .Cells) Is Nothing Then
If Target = "Reporting" Then
On Error Resume Next
Application.EnableEvents = False
With .Offset(0, 5)
.NumberFormat = "dd/mm/yyyy"
.Value = Now
End With
Application.EnableEvents = True
End If
End If
End With
End Sub