Hello,
here is a macro that will change the Today() formula to the non-changing absolute date when the cell to the left is changed to Yes.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A15")) Is Nothing Then
Application.EnableEvents = False
If Target = "Yes" Then Target.Offset(0, 1).Value = Format(Now, "mm/dd/yy")
Application.EnableEvents = True
End If
End Sub
The macro monitors the range A1 to A15 for changes. Adjust to your needs.
Copy the code, then right-click on the sheet tab, select "View Code" and paste the code into the code window. Make the required cell reference changes and close the VBE.
Test it. You will not see an immediate difference, but when you click a cell in column B where A has a Yes, you will see that the formula bar shows the date, not the Today() formula.
cheers,
Bookmarks