|
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Then Exit Sub
Select Case .Column
Case 18: .Offset(0, -17).Value = Date
Case 6: .Interior.Color = 65535
Case 15: .Interior.Color = 62885
Case 13: Call calc(11, 13, .Row, 14)
Call calc(9, 13, .Row, 17)
End Select
End With
End Sub
Function calc(s, e, R, c)
Dim i As Long
Dim j As Long
Dim k As Long
Dim V() As Variant
On Error Resume Next
ReDim V(s To 14)
For i = s To e
V(i) = Split(Cells(R, i), vbLf)
k = Application.Max(k, UBound(V(i)))
Next i
V(14) = Split(LTrim(Application.Rept(" 0", k + 1)), " ")
For i = 0 To k
V(14)(i) = 0
For j = s To e
V(14)(i) = V(14)(i) + Val(V(j)(i))
Next j
Next i
Cells(R, c) = Join(V(14), vbLf)
On Error GoTo 0
End Function
Sub SheetReset()
With Columns("C:C").Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
|
|