VBA code to look in range and copy if value is below something

[CV899000]

I am trying to get a VBA code to work.

I have a sheet with a coloumn (A) with a header called: "Documents - close to deadline"

I then have

Coloumn C is "weeks until deadline"

Coloumn D is "Document name".

I can have any number of documents (not predetermined) depending on the project or the state of the project.

What I want is for excel to automatically copy the "Document name" into "Documents - close to deadline" if the "weeks until deadline" is below 2.

I can write a code that does that:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("C2").Value < 2 Then
  Range("D2").Copy Range("A1").Offset(1, 0)
  End If
End Sub

My problem is that it does not update as I want it to.
Let me try to explain:

Situation 1:C2=2
C3=2
C4=4
C5=4

D2=Document1
D3=Document2
D4=Document4
D5=Document5

If I then change C2 to 1, D2 is copied to A2, and if I then change C3 to 1, D3 is copied to A3. That works fine.

Now, if I then delete the number in C2 to indicate that the document has been finished, I want the excel to notice that and make the content of A3 jump up to A2.

As it is now, if I delete the "1" in C2, then the "Document1" still remains in A2.

 

[JackDanIce]

Do you mean if you clear a cell in column C, you want to delete across the row from A to D and shift the cells below up?

E.g. You clear C2, the code deletes A2:D2 and shifts the data below up