Time Stamp

[toddlb83]

Hi All,

I am trying to write a function that creates a timestamp when a certain word is entered in a specific cell. The problem is, the value of the cell that the timestamp is based on is constantly changing. I want the timestamp to record the date the first time the formula is found true, and then keep that result.

here is the formula i have worked up so far: =IF(B4:B9="Complete",TODAY(),"")

 

[Joe4]

Welcome to the Board!

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range

'   Check for changes in column B
    Set rng = Intersect(Target, Range("B4:B1000"))
    
'   If no changes between B4 and B1000, exit sub
    If rng Is Nothing Then Exit Sub
    
'   Check to see if value is "Complete" and if so, add date stamp to column G
    Application.EnableEvents = False
    
    For Each cell In rng
        If cell.Value = "Complete" Then cell.Offset(0, 5).Value = Date
    Next cell
    
    Application.EnableEvents = True
    
End Sub

This should do what you want automatically.