Excel HELP

[Solved/Closed]
Report
-
 CQB -
Hello,

If possible, can you help me in coding this:

Collumn J will have the drop down menu of Open, In Progress, Complete [Open as default]

And I need K and L to record when the changes is made as an example.

Task J2 is in progress, then K2 should record the time for that specific task and should not change once time is recorded.

When task J2 is completed, then L should record the time for that task and should not change.

Thanks!

7 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
suppose validation are there in column J then try this macro (this will succeed only as far as J cell are under validation

right click sheet tab and click view code and in that window copy paste this event code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> Range("J1").Column Then Exit Sub

Application.EnableEvents = False
Cells(Target.Row, "K") = CDate(Now)
With Cells(Target.Row, "k")
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Cells(Target.Row, "L") = Target.Value
Range("J1:L1").EntireColumn.AutoFit
Columns("K:K").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub
If by Validation, having the drop down menu, then yeap it is included on there.

Unfortunately, it may need a little tweeking because if J2 is 'In Progress' then K2 records the time and L2 copies J2 = 'In Progress'.

What is needed is to have K2 record the time if J2 is 'In Progress' and have L2 record the time if J2 is 'Completed'.

Thanks!!
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
is this what you want? remember the spelling of the validation list and the word given in the macro should be exact and also case sensitive
"In Progress" is different from "in progress"(differences in case)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> Range("j1").Column Then Exit Sub

Application.EnableEvents = False

Range(Target.Offset(0, 1), Target.Offset(0, 2)).Cells.Clear
If Target.Value = "In Progress" Then
Cells(Target.Row, "K") = CDate(Now)
With Cells(Target.Row, "k")
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End If
If Target = "Complete" Then
Cells(Target.Row, "L") = CDate(Now)
With Cells(Target.Row, "L")
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End If
Range("J1:L1").EntireColumn.AutoFit
Columns("K:K").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
one correction
replce this statemnt

Columns("K:K").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"


as
Columns("K:L").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"


just change "K:K" and "K:L"
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
sorry my brain is sluggish today

just change "K:K" to "K:L"
Hey Venkat1926,

First I want to say THANKS!!!! for all the help. We still need last tweeking on the code:

I need the time that was in K/L to stay and not disappear/change. Because I need to record the actual times of when the task/row was started and finished.

Thanks again. I hope to hear from you soon.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
does it change???idid you check
as long as you do not change the value of J which has ALREADY BEEN filled by selection of validation.


see my code
Cells(Target.Row, "L") = CDate(Now)
With Cells(Target.Row, "L")
.Copy
.PasteSpecial Paste:=xlPasteValues

it is true first it find current time but it copied it on itself as pastevalues
so that entry is no more "now"

explain with one or two examples please
When I tried the code above, it records the times correctly:
When J2 was placed 'In Progress' it recorded the proper time in column K. But once the task J2 was placed 'Complete' then it removes the time in Column K and record the proper time in L. I need the time in K to stay the same after J2 was changed to complete.

Example:
Let's say I have 10 tasks that is in row 1-10: Task 1,2,3 is now 'In Progress' and I need column K to record the times of tasks 123 when it was started.

And then once 'Completed', I need column L to record the finish time. But I need both times to stay in the proper column.

Thanks!
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
I am rephrasing the condition

if j is open or in progress then k should lime (which should not change for any subsequent change in that J value) and L should be "open" or "In Progress"

once j is completed corresponding k value will r emain same as time for in progress and L should have completed time

see whether this macro does what you want!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> Range("j1").Column Then Exit Sub

Application.EnableEvents = False
If Target = "Open" Or Target = "In Progress" Then
'Range(Target.Offset(0, 1), Target.Offset(0, 2)).Cells.Clear
'If Target.Value = "In Progress" Then
Cells(Target.Row, "K") = CDate(Now)
With Cells(Target.Row, "k")
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Cells(Target.Row, "L") = Target
End If
If Target = "Complete" Then
Cells(Target.Row, "L") = CDate(Now)
With Cells(Target.Row, "L")
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End If
Range("J1:L1").EntireColumn.AutoFit
Columns("K:K").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub
perfect! thanks a lot!