keep date today without change when date is today

[Hasson]

Hi expert

here is the code when the column C contains DONE word , then will populate date (today) for adjacent cells for column D . my problem the date will change if I press inside the cells into column C without change word . it should keep old date without change date .
for instance :
cells in column D contain DONE word and date 02/12/2023
and tomorrow should keep DONE word and date 02/12/2023 without any change , but if I try changing NOT YET word to DONE word , then should populate date (today).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Me.Range("C14:C200")) Is Nothing Then
        Dim cell As Range
        For Each cell In Application.Intersect(Target, Me.Range("C14:C200"))
            If cell.Value = "DONE" Then
                cell.Offset(0, 1).Value = Date
            ElseIf cell.Value = "NOT YET" Then
                cell.Offset(0, 1).ClearContents
            End If
        Next cell
      
    End If

End Sub

 

[James006]

Hi,

You could test following

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("C14:C200")) Is Nothing Then Exit Sub
    With Target
        If .Value = "DONE" Then
            .Offset(0, 1).Value = Date
            .Offset(0, 1).Value = .Offset(0, 1).Value
        ElseIf .Value = "NOT YET" Then
            .Offset(0, 1).ClearContents
        End If
    End If
End Sub

 

[Hasson]

thanks
there is no difference with comparison the original code, sorry !
and you have typo .you should clear END IF in the end code and replace with END WITH

 

[James006]

Good you could spot the typo .. Sorry....

But have you spotted the difference ...? (hint : 6th line)

Have you tested it ???

 

[Hasson]

But have you spotted the difference ...? (hint : 6th line)

that's why there is no difference , it continues populating Date (today)
have you check your mistake?

Have you tested it ???

yes I changed regional setting for date in my laptop to see how works.
based on (hint : 6th line) logically continues populating date(today) because of from the base depends on date(today) this is what I think .

 

[James006]

You could test following

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("C14:C200")) Is Nothing Then Exit Sub
    With Target
        If UCase(.Value) = "DONE" And Not IsEmpty(.Offset(0, 1)) Then Exit Sub
        If UCase(.Value) = "NOT YET" Or IsEmpty(.Value) Then
            .Offset(0, 1).ClearContents
        ElseIf UCase(.Value) = "DONE" Then
            .Offset(0, 1) = Date
        End If
    End With
End Sub

 

[Hasson]

Finally it works!
Thanks so much