Need VBA Code for Project Tracker

[MGP22]

Hi!

Basically, I have an Excel sheet for project tracking and when a project is marked 'completed' I want it to automatically move to the "Closed" tab. I was able to get a code to work from a previous poster, but have no idea how to write my own so I can't edit it to fit my needs exactly.

Here is what I am currently working with...

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("I:I")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here...)
If LCase(Target.Value) = "completed" Then
With Target.EntireRow
.Cut Sheets("Closed").Cells(Rows.Count, "A").End(xlUp).Offset(1)
.Delete
End With
End If
End Sub
​

This code does work, however my first issue is that I don't want it to take the entire row, I would like to specify whatever #row it is (IE- will not always be row 4 that has most completed project) and cells A-K.

A bonus request would be once the project is moved to the "Closed" tab from "Upcoming & Open", column L of the "Closed" tab would have a rule that if the "Closed" tab's column I ("Status") is set to "Completed" than Column L is mandatory & must be filled out. Column L in this scenario is my "Completed On" date column (makes sense? Mark it completed, you must tell me the date it was completed on)

Last, once its moved to the "closed" tab, I would like a similar rule to the first set up so that if there is an issue with a project, it can easily be moved back. Basically, if on the closed tab the status (column I) is changed to "In Progress" or "Upcoming", it moves it back to the "Upcoming & Open" Tab.

I think these three requests are pretty easy, I'm just very new to VBA/Macros- hopefully one of you can help!