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]

Thank you Joe4, you are right its a bit complicated. But I apreceiate the help. If you come up with something more simple let me know.

There really is no more "simple" way under the current structure - that is the way it would have to be done.

The only other things I can think of are:

1. To log (time stamp) every single addition/change you make, and have your formulas pull the latest time stamp for the logged entries meeting those particular conditions. More of a Database approach (which might be a better structure for your project anyway, which would work better in a Relational Database program like Access or SQL).

2. If you have a User Form for entry, maybe have that update the appropriate Time Stamp in C8:C13 directly at that time, as entries are being made.

 

[SantanaKRE8s]

Ok instead, can this be possible to add a timestamp in cel "Z1" when any change is made to any cell in the sheet "BACKLOG" instead of just doing it for a specific saection.

Private Sub Worksheet_Change(ByVal Target as Range)

If Intersect(Target, Range("C:AX")) Is Nothing Then Exit Sub

Range("Z1").Value = Now

End Sub

 

[Joe4]

What sheet is this cell "Z1" you want to update on?
Is that also on the "BACKLOG" sheet, or another sheet?

 

[SantanaKRE8s]

Yes its also in "BACKLOG"

 

[Joe4]

OK, if you truly want it to be run on the manual update of ANY cell in the sheet, then use this code:

Private Sub Worksheet_Change(ByVal Target as Range)
    Application.EnableEvents = False
    Range("Z1").Value = Now
    Application.EnableEvents = True
End Sub

 

[SantanaKRE8s]

Thanks, this code I add it on "this workbook" or in a new module?

 

[Joe4]

Thanks, this code I add it on "this workbook" or in a new module?

Neither. It needs to go in the Sheet module for the "Backlog" sheet, if you want it to be automated!
Event procedures, which are VBA codes that are triggered automatically, only work when placed in the correct location and given the correct name.

An easy way to make sure you put it in the correct place is to go to the Backlog sheet, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the code in the VB Editor window that pops up. That will be the correct sheet module it needs to go in to.

 

[SantanaKRE8s]

In that sheet module I already have another Code, how do I add this one?

 

[Joe4]

What does it look like?

 

[SantanaKRE8s]

Its a code to remove the entire ROW when its set to resolved.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim cell As Range

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

' Exit if no updates to column S
If rng Is Nothing Then Exit Sub

' 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 Sub