How to know if cell value really changed?

[dma431]

Hello,

I'm using very simple code to add the current date to a cell when the adjacent cell was modified.
It works well, except when you select the value and click the formula bar, without making any changes to the actual value, then click on some other cell.
When that happens, it assumes the value changed, and refreshes the date next to it.

I was wondering if there's a way to check if the value remained the same after clicking away, and if it did, keep the date value intact.

Here's the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
    If Cell.Column = Range("C:C").Column Then
        If Cell.Value <> "" Then
            Cells(Cell.Row, "D").Value = Now
        Else
            Cells(Cell.Row, "D").Value = ""
        End If
    End If
Next Cell
End Sub

Thanks.

 

[Fluff]

Are you changing multiple cells at once, or just one by one?

 

[dma431]

Are you changing multiple cells at once, or just one by one?

One at a time.

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim CurrentValue As Variant, OldValue As Variant
   
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("C:C")) Is Nothing Then
      If Target.Value = "" Then
         Target.Offset(, 1).Value = ""
      Else
         CurrentValue = Target.Value
         With Application
            .EnableEvents = False
            .Undo
            If Target.Value <> CurrentValue Then
               Target.Value = CurrentValue
               Target.Offset(, 1).Value = Now
            End If
            .EnableEvents = True
         End With
      End If
   End If
End Sub

 

[dma431]

That works great! Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[zinvie]

Hi, I am riding on this thread as I have a similar challenge.
I am doing a sales funnel, where I have a dropdown list of stages in column G, and any changes are reflected in column H.
The problem I have is whenever someone clicks on the individual cell but did not make any changes (e.g. click on a cell titled "prospect", but choose "prospect" from the list), the date would automatically update itself.

How do I amend the following code so that there will only be a change in date if the value in the cell changes (e.g. from prospect to qualify)? Any advice?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("G:G"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub

 

[Fluff]

Please start a thread of your own for this question. Thanks