auto running macro

[Maryna]

I have to have the date added automatically in column H if the info is added
in column F of the same row. The code I wrote works but I have to return to
column F after the info is added in F and only after that reverse-move does
the required info appear in H
Where is my mistake and how to fix this problem?
Thanks!
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
ApplicationEnableEvents = False
Range("H:H").NumberFormat = "mm/dd/yyyy"
For Each myCell In Intersect(Target, Range("F:F"))
rw = myCell.Row
Cells(rw, "H").FormulaR1C1 = "=IF(RC[-2]<>"""", Now(), """")"
Cells(rw, "H").Value = Cells(rw, "H").Value
Cells(rw, "M").Value = Environ("UserName")
Next myCell
Application.EnableEvents = True
End Sub

 

[Per Jessen]

Hi

You are using 'Worksheet_SelectionChange' event but you should use
'Worksheet_Change' event.


So just remove 'Selection' from the line below....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Regards,
Per

 

[Gord Dibben]

Wrong event type.

Also some unneeded stuff in there with the formulas and change to values.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Target
If .Value <> "" Then
.Offset(0, 2).Value = Format(Now, "mm/dd/yy")
.Offset(0, 7).Value = Environ("Username")
End If
End With
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP