Automaticaly Enter Date in selected Cell


G

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("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?
 
Ad

Advertisements

J

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("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
 
Ad

Advertisements

G

Guest

Worked perfectly thanks a lot!!!

JE McGimpsey said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top