Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, B As Range, Inte As Range, r As Range
Set A = Range("E:E,J:J,N:N,R:R,U:U,X:X,AF:AF,AJ:AJ,AM:AM,AP:AP,AT:AT,AW:AW,AZ:AZ,BC:BC,BG:BG,BJ:BJ")
Set Inte = Intersect(A, Target)
If Inte Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In Inte
If ((r.Value > 0) And (r.Offset = "")) Then
r.Offset(0, 1).Value = Date
r.Offset(0, 1).NumberFormat = "dd-mm-yyyy"
r.Offset(0, 2).Value = Time
r.Offset(0, 2).NumberFormat = "hh:mm:ss AM/PM"
Else
r.Offset(0, 1).Value = ""
r.Offset(0, 2).Value = ""
End If
Next r
Application.EnableEvents = True
End Sub
Above is the code I am using (credit: Adnan Shahid) . I am having trouble with a nested If statement specifically the (r.Offset = ""). The end game is that when a value is entered in the specified ranges the date and time auto-populate. However, I would also like to make it so that if a user makes a mistakes and changes the value in the range the initial date and time given does not change.
Aucun commentaire:
Enregistrer un commentaire