Forum Discussion
insert date when giving "OK" command
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"
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- 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.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.
