insert date when giving "OK" command

[Alan_JN]

Hello everyone, How do I mark the date that I give "OK" in a cell to appear a specific text (in another cell) along with the date that was given that "OK"? For example. "paid on 10-10-2023"

[#2]

@Alan_JN 

Let's say you enter OK in column D, from D2 down, and you want the date plus text in column E.

Set the number format of column E to the custom format

"Paid on "dd-mm-yyyy

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("D2:D" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("D2:D" & Rows.Count), Target)
            If rng.Value = "OK" Then
                rng.Offset(0, 1).Value = Date
            Else
                rng.Offset(0, 1).ClearContents
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

[#3]

Hello Hans,

Thanks for your support!

I followed the step by step you told me, but the date still does not appear. How do I "Set column E numeric format to custom format

"Paid on" dd-mm-yyyy "? like you said.

If there is a way in which I put OK in column D and in column E the date appears (without changing as the days go by), that's fine.

[#4]

@Alan_JN 

Select column E, then click the arrow in the lower right corner of the Number group on the Home tab of the ribbon.

Select Custom in the Category list, then type

"Paid on"dd-mm-yyyy

in the Type box.

Click OK.

See the attached demo workbook. You'll have to allow macros when you open it.

[#5]

@HansVogelaar 

Oh yeah! thank you very much for your support!! 

Grateful,

Alan J. Nascimento