Time Stamp when cell value is changed

[SantanaKRE8s]

I have a VBA code to add a time stamp when ever the value of a cell is changed, but I am using a Count function in the cell, so I noticed the time stam is not working. Is there a way to make it work with the Function so that this is being modified automatically.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyDataRng As Range
Set MyDataRng = Range("C8:C13")

If Intersect(Target, MyDataRng) Is Nothing Then Exit Sub

On Error Resume Next
If Target.Offset(0, 1) = "" Then
Target.Offset(0, 1) = Now
End If

Target.Offset(0, 2) = Now

End Sub

 

[Joe4]

You can only have one "Worksheet_Change" event procedure per sheet, so you have to combine them into one, like this:

Private Sub Worksheet_Change(ByVal Target As Range)

'***BLOCK 1***
Dim rng As Range
Dim cell As Range

' See if any updates to column S
Set rng = Intersect(Range("X:X"), Target)

' What to do if update in column X
If Not rng Is Nothing Then

    ' Loop through values updated in column S
    Application.EnableEvents = False
    For Each cell In rng
        ' See if cell value is Resovled
        If cell.Value = "Resolved" Then
            ' Copy to bottom of resolved sheet
            Rows(cell.Row).Copy
            Sheets("Resolved").Range("A" & Sheets("Resolved").UsedRange.Rows.Count + 1).PasteSpecial _
                Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
            ' Remove from backlog sheet
            Rows(cell.Row).Delete
        End If
    Next cell
    Application.EnableEvents = True
End If

'***BLOCK 2***
' Update cell Z1 with any cell change
Application.EnableEvents = False
Range("Z1").Value = Now
Application.EnableEvents = True

End Sub

 

[SantanaKRE8s]

Awesome ! bboth codes working, Thank you for your patience Joe4

 

[SantanaKRE8s]

You can only have one "Worksheet_Change" event procedure per sheet, so you have to combine them into one, like this:

Private Sub Worksheet_Change(ByVal Target As Range)

'***BLOCK 1***
Dim rng As Range
Dim cell As Range

' See if any updates to column S
Set rng = Intersect(Range("X:X"), Target)

' What to do if update in column X
If Not rng Is Nothing Then

    ' Loop through values updated in column S
    Application.EnableEvents = False
    For Each cell In rng
        ' See if cell value is Resovled
        If cell.Value = "Resolved" Then
            ' Copy to bottom of resolved sheet
            Rows(cell.Row).Copy
            Sheets("Resolved").Range("A" & Sheets("Resolved").UsedRange.Rows.Count + 1).PasteSpecial _
                Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
            ' Remove from backlog sheet
            Rows(cell.Row).Delete
        End If
    Next cell
    Application.EnableEvents = True
End If

'***BLOCK 2***
' Update cell Z1 with any cell change
Application.EnableEvents = False
Range("Z1").Value = Now
Application.EnableEvents = True

End Sub

Can this be set so it only shos the date and not the time?

 

[Joe4]

Can this be set so it only shos the date and not the time?

Sure. Just change this line:

Range("Z1").Value = Now

to this:

Range("Z1").Value = Date