VBA multi timestamp option?

[whodey33]

Hello people way more skilled than this guy.

I'm trying to create a bit of a call/issue tracker making it as simple as possible for those taking the calls to complete and get me useful data. I'm totally a google stealing machine and don't know much but when it works it works.

So I found a VBA formula that looks at lets say column A and if it has a # it will auto populate B (e.g. A1 has 1, B1 gets static timestamp)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
End With
End Sub

This seems to work perfectly however I also want to use it for column F and G. Column F has a data validation list for in progress/completed options. When that option is selected, updated, I want column G to get the timestamp. That works if I just use:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 6 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
End With
End Sub

I've been able to do one or the other but not sure how to do both. Is there a specific statement I need to add to run the first and then the second? Any help would be glorious! If anyone knows of another way that would be super....I was trying functions but the date auto updated which didn't want. Looking down the road, I want to be able to see the time the call/issue came in to the time resolved.

Thank you for any help!

 

[Joe4]

Welcome to the Board!

This will handle both columns:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If (Target.Column = 1) Or (Target.Column = 6) Then
    With Target.Offset(0, 1)
        .Value = Now
        .NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
    End With
End If

End Sub

 

[whodey33]

Well thank you good sir...I knew it was going to be something simple! Works perfectly!

 

[Joe4]

You are welcome!
Glad I was able to help.