auto set time(now) if criteria=true with no later change

[Guest]

I am using excel to manage a continues progress report on a data base of
users. The report includes set of stages that the users are going through.
Each time a define criteria set on (true), the user is moving from one stage
to the next.

I have a cell in which I mark the entry day (the day the user enter the new
stage). Once the date was set, it should be constant and should not change in
the future, even if the criteria was change.

I am looking for a automated way to set the date (today) but once set, stay
as a constant.

Using “ =IF(G7+H7+I7 >= 1,TODAY(),"") †will set the date but once I change
the values in G7/H7/I7 the date is being re-set.

 

[Guest]

I'd probably use something like a Worksheet_Change event for this. These are
executed when something is changed on a worksheet. You can selectively run
the code based on which cells change, etc.

See example below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myrange As Range
Dim r As Range
Dim EmptyRange As Boolean
Set myrange = Range("G7:I7")

if not intersect(target,myrange) is nothing then
EmptyRange = False
For Each r In myrange
If IsEmpty(r) Then
EmptyRange = True
Exit For
End If
Next r

If EmptyRange Then
Cells("J7").Value = WorksheetFunction.Text(Now, "mm-dd-yyyy")
Else
'Whatever you'd do if you already have something in all cells.
End If
End if
End Sub