Change the value of a drop down menu based on the value of another cell

[Kgreen214]

Hello,

I'm looking for help. I need to set up a VBA code that will automatically change the value of column J. Column J is a drop down list and I need it to update to "Needs Update" when column G changes to "Overdue". The cells in column G are coded with a (if) formula that changes the value of the cell to overdue when the date in column F has passed.

Currently the below VBA code is set up for each worksheet and are assigned to a button. So I need the new code to work with this code which is designed to copy a task to another sheet (summary agenda) with out deleting the line task but updating column J to be blank once moved.

Sub Test()

Application.ScreenUpdating = False

With Sheet4.[A8].CurrentRegion
.Columns(2).Hidden = False
.AutoFilter 10, "*" & "Update" & "*"
.Columns("A:K").Offset(1).Copy
Sheet3.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
.Columns("J").Offset(1).Value = ""
.Columns("F").Offset(1).Value = "New Date Needed"
.AutoFilter
.Columns(2).Hidden = True
End With

Sheet3.[A4].CurrentRegion.Offset(1).Sort Sheet3.[A5], 1
Sheet3.Columns.AutoFit

Application.ScreenUpdating = True

End Sub

If someone could help me with this code that would be great. Attached is the screen shot of the worksheet I am referring to. Thanks!