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("D2:D100"), .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?

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("D2:D100"), .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

In article ,
Ceptor54 wrote:

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("D2:D100"), .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?

Worked perfectly thanks a lot!!!

"JE McGimpsey" wrote:

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("D2:D100"), .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

In article ,
Ceptor54 wrote:

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("D2:D100"), .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?