Date-time updates every cell?

[Selah West]

I have a worksheet that has a formula in all cells in column H that says if
there is a comment in column G then put the current date/time in H(current
cell). My formula looks like the following in H5;

=IF(ISBLANK(G5),"",NOW())

The problem is that if I enter a comment in any cell in column G it updates
all of my dates in column H.

Using Excel 2007

 

[Rick Rothstein]

The NOW() and TODAY() functions are meant for you to have formulas take
action action the current date (and/or date-time), not as a time stamp. You
can do what you want using VBA event code. Delete all of your formulas in
Column H, right click the tab at the bottom of your worksheet, select View
Code from the popup menu that appears and copy/paste the following code into
the code window that opened up...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
Application.EnableEvents = False
If Target.Value <> "" Then
Target.Offset(, 1).Value = Now
Else
Target.Offset(, 1).Value = ""
End If
Application.EnableEvents = True
End If
End Sub

Now, when you go back to your worksheet and enter something in Column G, the
date and time will be placed next to it in Column H. As written, if you
delete an entry in Column G, the date that was originally displayed for the
comment in Column H will be deleted as well.