Update date in cell automatically if other cell contains text

[lunatu]

Hi,

is it possible to add current date automatically in cell if another cell contains specific text? For example if cell in column B contains text "open" the current date is automatically updated in cell A. And is the cell in column B changes for to "Close" the date in column A remains as it was.

Any ideas is there a way to solve this?

 

[mohadin]

HI
Try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [B:B]) Is Nothing Then
        If UCase(Target) = UCase("open") Then
            Target.Offset(, -1) = Now()
        End If
    End If
End Sub

 

[mohadin]

For only date (without time) try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [B:B]) Is Nothing Then
        If UCase(Target) = UCase("open") Then
            Target.Offset(, -1) = int(Now())
        End If
    End If
End Sub

 

[lunatu]

Works perfectly, thanks for your help!

 

[mohadin]

You are welcome
And thank you for the feedback
Be happy and safe
HAPPY NEW YEAR