How can I plot more than one value in the same date?

Specific value returns a date & time

• QUESTION: In my spreadsheet I want to get the date & time when that particular invoice is paid. For example in L5 I select "Paid" (from drop down menu) then M5 should show today's date & time (this should not change everytime I open the spreadsheet). I had seen your post for similar query so I though you might be able to help me out on this one as well. Rikin ANSWER: Hello Rikin, So I am guessing you want that if you select any cell in column L and you select Paid from the dropdown, you want a hard coded date in the same row in column M. right click on the sheet tab and select view code. Then put in this code Private Sub Worksheet_Change(ByVal Target As Range) If Target.count > 1 then exit sub If Target.column = 5 then if Target.Value = "Paid" Then Cells(Target.row,"M").Value = Now Cells(Target.row,"M").Numberformat = "dd/mm/yyyy hh:mm" Columns(13).Autofit End if End if End Sub If that isn't what you want, then post back. Change the Numberformat to reflect the way you want your date and time to appear. ---------- FOLLOW-UP ---------- QUESTION: Thanks It certainly works for me however, I had to change the column number to 12 from 5. One more addition to the question - after selecting Paid for some reason I delete paid can this date in column 13 automatically get deleted. I tried following coding in VBA but didnt work: If IsEmpty(Target.Column) Then Target.Column.ClearContents Please let me know. Rikin ANSWER: Rikin, apologies for the column = 5. I was answering another question that dealt with column E and I guess it was still on my mind. Private Sub Worksheet_Change(ByVal Target As Range) If Target.count > 1 then exit sub If Target.column = 12 then if Target.Value = "Paid" Then Cells(Target.row,"M").Value = Now Cells(Target.row,"M").Numberformat = "dd/mm/yyyy hh:mm" Columns(13).Autofit Else if Target.Value = "" and Target.offset(0,1).Text <> "" then Target.Offset(0,1).ClearContents End if End if End Sub ---------- FOLLOW-UP ---------- QUESTION: Thanks for your reply but your code is giving me an error on following line: Else if Target.Value = "" and Target.offset(0,1).Text <> "" then This line is red in VBA and when I run the code it gives me a syntax error.

• Answer:

  Rikin, must have been a typo. "Else If" should be one word The below is tested and worked for me. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Column = 12 Then If Target.Value = "Paid" Then Cells(Target.Row, "M").Value = Now Cells(Target.Row, "M").NumberFormat = "dd/mm/yyyy hh:mm" Columns(13).AutoFit ElseIf Target.Value = "" And Target.Offset(0, 1).Text <> "" Then Target.Offset(0, 1).ClearContents End If End If End Sub