macro error

[mwvirk]

below macro should give me current time in cell range 'O' when i add value in 'N' but its not giving any error or time in cell 'O'
pls help

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim irow As Long
If Not Intersect(Target, Range("I:I")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
irow = Target.Row
If IsEmpty(Target) Or IsEmpty(Target.Offset(0, -8)) Then
    Target.Offset(0, 2).ClearContents
    GoTo EndProc
End If
If IsDate(Target.Offset(0, 2)) Then GoTo EndProc
If Not IsEmpty(Target.Offset(0, -8)) Then
Target.Offset(0, 2) = Date
Else: GoTo EndProc
End If
If Not Intersect(Target, Range("N:N")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
irow = Target.Row
If IsEmpty(Target) Or IsEmpty(Target.Offset(0, -13)) Then
        Target.Offset(0, 1).ClearContents
    GoTo EndProc
End If
'If IsDate(Target.Offset(0, 1)) Then GoTo EndProc
If Not IsEmpty(Target.Offset(0, -13)) Then
Target.Offset(0, 1) = Time
Else: GoTo EndProc
End If
End If
End If
'---------------------------------
If Not Intersect(Target, Range("T:T")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
irow = Target.Row
If IsEmpty(Target) Then
    Target.Offset(0, 1).ClearContents
        GoTo EndProc
End If
If IsDate(Target.Offset(0, 3)) Then GoTo EndProc
If Not IsEmpty(Target.Offset(0, -19)) Then
Target.Offset(0, 1) = Date
Else: GoTo EndProc
End If
End If
EndProc:
Application.EnableEvents = True
End Sub

 

[shg]

Maybe like this, but I had difficulty following the logic.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r           As Range
    Dim cell        As Range
    Dim iRow        As Long
 
    Set r = Intersect(Target, Range("I:I, N:N, T:T"))
    If r Is Nothing Then Exit Sub
 
    On Error GoTo Outtahere
    Application.EnableEvents = False
 
    For Each cell In r
        iRow = cell.Row
        Select Case cell.Column
            Case 9    ' I
                If IsEmpty(cell.Value) Or IsEmpty(Cells(iRow, "A")) Then
                    Cells(iRow, "K").ClearContents
                ElseIf Not IsDate(Cells(iRow, "K")) And _
                       Not IsEmpty(Cells(iRow, "A")) Then
                    Cells(iRow, "K").Value = Date
                End If
 
            Case 14    ' N
                If IsEmpty(cell.Value) Or IsEmpty(Cells(iRow, "A")) Then
                    Cells(iRow, "B").ClearContents
                ElseIf Not IsEmpty(Cells(iRow, "A")) Then
                    Cells(iRow, "O").Value = Time
                End If
 
            Case 20    ' T
                If IsEmpty(cell.Value) Then
                    Cells(iRow, "U").ClearContents
                ElseIf Not IsDate(Cells(iRow, "U")) And _
                       Not IsEmpty(Cells(iRow, "A")) Then
                    Cells(iRow, "U").Value = Date
                End If
        End Select
    Next cell
 
Outtahere:
    Application.EnableEvents = True
End Sub

 

[mwvirk]

good. but when deleting value from N its not clearing values from O
other than N if u delete value from I then it clears K and same like this, if u remove value from T then it clears U but not in O when clearing N

 

[mwvirk]

ok. u put 'B' there... its ok now. thnx