Date protection

Posted by Thomas Rector on October 20, 2001 5:15 AM

Formula in cells B7 through B26, =if(A7>" ",NOW()," ") works great.

However any cell changes in A8 through A26 will change "ALL" the dates in B7:B26.

I need each cell in B7:B26 to retain the Date value that was created by the "if" formula. So that
someone updates A11 three days after A7, the date in
B7 will not change.

Or a work around, so users don't have to type the date.

Thanks for help

Posted by Tom Morales on October 20, 2001 6:58 AM

Thomas,
One way to skin this cat would be to use a macro. Right-click your worksheet tab, and select "view code". Insert this macro:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
dater = Int(Now)
ds = Array(7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26)
For Each d In ds
a = "$A$" & d
b = "$B$" & d
If Target.Address = a Then
If Not Target.Formula = "" Then Range(b).Value = dater
End If
Next

There may be more elegant solutions, but it'll work.
Tom
End Sub

Posted by Tom Morales on October 20, 2001 7:01 AM

Oops. The "End Sub" got separated from the macro.