Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A2")) Is Nothing Then Exit Sub
Dim c As Range
With Range("C4:C34")
.UnMerge: .ClearContents: .Borders(xlInsideHorizontal).LineStyle = xlNone
End With
Range("I4:I34").ClearContents
If Weekday([A4], vbMonday) < 6 Then
[C4] = DatePart("ww", [A4], vbMonday, vbFirstFourDays)
With Range([C4], [C4].Offset(5 - Weekday([A4], vbMonday)))
.Merge
.Borders.Weight = xlMedium
End With
End If
For Each c In Range("A4:A34")
If Weekday(c, vbMonday) = 1 Then
c.Offset(0, 2) = DatePart("ww", c, vbMonday, vbFirstFourDays)
With Range(c.Offset(0, 2), IIf(c.Row > 29, Range("C34"), c.Offset(4, 2)))
.Merge
.Borders.Weight = xlMedium
End With
ElseIf Weekday(c, vbMonday) = 6 Then c.Offset(0, 8).FormulaR1C1 = IIf(c.Row > 8, "=SUM(R[-5]C:R[-1]C)", "=SUM(R4C:R[-1]C)")
End If
Next c
End Sub