I have the following code that help me record the date changes in certain columns per project type( that is in Column A ) and save those dates historically in certain columns in sheet2, stacked in one cell to keep the history of it per project type.


What I need help with is the following

1) On my sheet 1, code works fine if dates are entered one by one manually, however code does not honor any formulas. i.e R2 = L2 +5 , and i would like the code to somehow accept the formulas so if I change one entry, other ones are changes as well.

2) Also one other thing is that my project names are created as following on Column W (W# = A#&c#) (i.e which also matches to the project names on sheet 2 on Column A starting A:26) . How can we do the name to match to Column W instead of Column A when comparisons are done. So in this case if the project name in sheet 2 column A match sheet 1 column W, then update the dates and honor the formulas for example...


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("K:L,R:R,U:U")) Is Nothing Then Exit Sub
Dim project As Range, rDate As Range
Set project = Sheets("Sheet2").Range("A27:A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row).Find(Cells(Target.Row, 1).Value, LookIn:=xlValues, lookat:=xlWhole)
If Not project Is Nothing Then
Select Case Target.Column
Case Is = 11
Set rDate = Sheets("Sheet2").Rows(26).Find(Cells(1, 11).Value, LookIn:=xlValues, lookat:=xlWhole)
Sheets("Sheet2").Cells(project.Row, rDate.Column) = Target & Chr(10) & Sheets("Sheet2").Cells(project.Row, rDate.Column)
Case Is = 12
Set rDate = Sheets("Sheet2").Rows(26).Find(Cells(1, 12).Value, LookIn:=xlValues, lookat:=xlWhole)
Sheets("Sheet2").Cells(project.Row, rDate.Column) = Target & Chr(10) & Sheets("Sheet2").Cells(project.Row, rDate.Column)
Case Is = 18
Set rDate = Sheets("Sheet2").Rows(26).Find(Cells(1, 18).Value, LookIn:=xlValues, lookat:=xlWhole)
Sheets("Sheet2").Cells(project.Row, rDate.Column) = Target & Chr(10) & Sheets("Sheet2").Cells(project.Row, rDate.Column)
Case Is = 21
Set rDate = Sheets("Sheet2").Rows(26).Find(Cells(1, 21).Value, LookIn:=xlValues, lookat:=xlWhole)
Sheets("Sheet2").Cells(project.Row, rDate.Column) = Target & Chr(10) & Sheets("Sheet2").Cells(project.Row, rDate.Column)
End Select
End If
End Sub