• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatic update current date and time in same row based on specific text input

IKHAN

Member
Hi,

If User types in "Yes" anywhere in Column ranging from G3 : G40 , Is there a macro that it would automatically update\write the current date and time in Column L

There is already a date and time in col L for assigned task. (Date format in col L is "ddd mmm dd, yyyy - hh:mm AM/PM")

Any assistance would be greatly appreciated.
 

Attachments

  • test.xlsx
    12.5 KB · Views: 3
Try this code
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Handler
    If Target.Column = 7 And Target.Value = "Yes" Then
        Application.EnableEvents = False
            If Len(Target.Cells(1).Value2) <> 0 Then
                Target.Offset(, 5).Value = Format(Now(), "dd-mm-yyyy hh:mm:ss")
            Else
                Target.Offset(, 5).Value = vbNullString
            End If
        Application.EnableEvents = True
    End If
Handler:
End Sub
 
Using above macro and subtracting date\ time, getting wrong data type error in another column.
tested manually entering date\time,works fine - but not with above macro.
 
Try this code
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Handler
    If Target.Column = 7 And Target.Value = "Yes" Then
        Application.EnableEvents = False
            If Len(Target.Cells(1).Value2) <> 0 Then
                Target.Offset(, 5).Value = Format(Now(), "ddd mmm dd, yyyy - hh:mm AM/PM")
                Target.Offset(, 6).Value = (Now() - Target.Offset(, 4).Value) * 1440
            Else
                Target.Offset(, 5).Value = vbNullString
            End If
    End If
Handler:
Application.EnableEvents = True
End Sub
 
Last edited:
Need to keep formula in Colmn M =($L3-$K3)*1440 , reason being users sometimes change date and time in colmn L manually and that reflects in M colmn.

The above code works but if user changes date and time manually, doesn't update in Colmn M.
 
Had seen a post somewhere,Cldn't find it anymore , where dates can be copied in same date number format instead of text.

Can this macro be modified to cature current date time in date number format?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Handler
    If Target.Column = 7 And Target.Value = "Yes" Then
        Application.EnableEvents = False
            If Len(Target.Cells(1).Value2) <> 0 Then
                Target.Offset(, 5).Value = Format(Now(), "dd-mm-yyyy hh:mm:ss")
            Else
                Target.Offset(, 5).Value = vbNullString
            End If
        Application.EnableEvents = True
    End If
Handler:
End Sub
 
Back
Top