I am trying to figure out what I am doing wrong with my code for trying to calculate the difference in days between 2 dates into a new column:
Private Sub Worksheet_Change(ByVal Target As Range)
Const RoughIn = 37
Const Drywall = 56
Const Trim = 71
Const Tile_TrimOuts = 91
Const PunchOut = 112
Const Completion = 116
Const Closing = 129
Dim rng As Range
Dim dtm As Date
Dim r As Long
If Not Intersect(Range("E2:E" & Rows.Count), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each rng In Intersect(Range("E2:E" & Rows.Count), Target)
r = rng.Row
If rng.Value = "" Or Not IsDate(rng.Value) Or rng.Offset(0, -1).Value = "" Then
Range("AJ" & r).ClearContents
Range("BF" & r).ClearContents
Range("BM" & r).ClearContents
Range("BO" & r).ClearContents
Range("CF" & r).ClearContents
Range("DN" & r).ClearContents
Range("EB" & r).ClearContents
Range("EF" & r).ClearContents
Else
dtm = rng.Value
Range("AJ" & r).Value = dtm + RoughIn
Range("BF" & r).Value = dtm + Drywall
Range("BM" & r).Value = dtm + Trim
Range("BO" & r).Value = Range("BN" & r).Value - Range("BM" & r).Value
Range("CF" & r).Value = dtm + Tile_TrimOuts
Range("DN" & r).Value = dtm + PunchOut
Range("EB" & r).Value = dtm + Completion
Range("EF" & r).Value = dtm + Closing
End If
Next rng
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
