Pasting won't trigger Worksheet_Change...HELP!

[bspace2]

Hi All,

I've searched on other posts and cannot come up the answer. I have the following code where I need the time (Now) to display when there is a value in Column O. However if something is pasted into O it does not trigger the Worksheet_Change. Please help!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim S As Range, O As Range, t As Range

Set S = Range("S:S")
Set O = Range("O:O")
Set t = Target

If Intersect(t, O) Is Nothing Then Exit Sub

Application.EnableEvents = False
Range("S" & t.Row).Value = Now
Application.EnableEvents = True
End Sub

 

[Fishboy]

Hi Fishboy,

One more question. The logic on this is working most of the time. However, if I type "RESOLVED" in a cell and hit Enter it's actually changing the cell one below that of which I typed into in Column S. If I type "RESOLVED" and arrow over, it's updating the correct corresponding cell in Column S. IF you know how to fix this, great. If not I appreciate your time in all this.

Thanks

OK, I think that will be because it is checking "each cell in selection", of which the current selection is now the row below. Try this modified version instead:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range


If Not Intersect(Target, Range("O:O")) Is Nothing Then
    If Target.Cells.Count > 1 Then
        For Each Cell In Selection
            Application.EnableEvents = False
            Range("S" & Cell.Row).Value = Now
            Application.EnableEvents = True
        Next Cell
    Else
        Range("S" & Target.Row).Value = Now
    End If
End If


End Sub