CODEWITHSUNDEEP
vbaexcel
P. Johnston
P. Johnston

Reputation: 15

Stop NOW() Function from Auto Updating

I have a cell which I want to record the time when adjacent cells to the left are changed. I do it with the NOW() function; however, the problem is that the time gets updated each time workbook is re-calculated. So, I am wondering whether there is any original way to prevent this very cell from auto-updating.

My current formula in the cell:

=IF(ISBLANK(H11),"",IF(H11="Interested",NOW(),IF(H11="Not Interested",NOW(),"")))

I personally have come up with this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Destination As Range
   If Not Intersect(Target, Range("H:H")) Is Nothing Then
      Target.Offset(0, 1).Value = Now
   End If
End Sub

My issue with this code is that it is looking for any data in the cell. I am only wanting the cell to record the time when it contains either "Interested" or "Not Interested". The cell that I am looking at currently contains "In-progress". I have tried playing around with my code to try and incorporate these criteria's but I keep getting hit with errors. Any advice on what I can do to fix this? Thanks in advance.

Upvotes: 1

Views: 2119

Answers (2)

Charles Williams
Charles Williams

Reputation: 23505

An alternative approach is a simple UDF that you use as =TimeChanged(H11)

Option Explicit
Option Compare Text
Public Function TimeChanged(theCell As Variant)
    If TypeOf theCell Is Range Then theCell = theCell.Value2
    If theCell = "Interested" Or theCell = "Not Interested" Then
        TimeChanged = Now
    Else
        TimeChanged = ""
    End If
End Function

Upvotes: 0

Ralph
Ralph

Reputation: 9434

Try the following code instead:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Destination As Range
    If Not Intersect(Target, Range("H:H")) Is Nothing Then
        If LCase(Trim(Target.Value2)) = "not interested" Or LCase(Trim(Target.Value)) = "interested" Then
            Application.EnableEvents = False
            Target.Offset(0, 1).Value = Now
            Application.EnableEvents = True
        End If
    End If
End Sub

enter image description here

Upvotes: 1

Related Questions