This seems like it would be simple. I have a project plan with due dates for each task in a column. In column "L" I am using a data validation list to show the current Status, giving only the options of Complete, In Process, Overdue, or Not Started. In column "I", I have a formula to calculate if a task is overdue (basically today - due date for any incomplete tasks), and it gives the number of days overdue.
I would like the Status to automatically update to "Overdue" in column L if the value in column I is > 0. I was able to make this happen using the following code for one cell. The issue is that if I then manually update cell L11, say if the task is now Complete, Excel throws an error, stops working, and restarts.
So two questions: How do I update the code so that it doesn't break Excel when I update the status, and what changes do I need to make for this code to work on every cell in column L, not only cell L11.
Private Sub Worksheet_Change(ByVal Target As Range) If Range("I11").Value > 0 Then Range("L11").Value = "Overdue" End If End Sub
First screen is showing that L11 updated to Overdue. Second screen is what happens if I manually select Complete from the drop-down in cell L11.