```
'#### This Worksheet Change sub checks to see if the =SUM() formula
'#### entered on this sheet includes all the rows with values
'#### above the formula in the column mentioned in the formula
'#### It will only work with simple =SUM(F2:F18) style formula
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sFrml As String, lRstrt As Long, lRend As Long, lC As Long
Dim rStrt As Range, rEnd As Range
With Target
If .HasFormula Then
If Left(.FormulaR1C1, 5) = "=SUM(" Then
' get column and start end rows of sum formula
sFrml = Mid(.FormulaR1C1, InStr(1, .FormulaR1C1, "R"), _
InStr(1, .FormulaR1C1, ")") - InStr(1, .FormulaR1C1, "R"))
'sFrml like: R[-9]C[1]:R[-1]C[1]
' but if column same as formula then: R[-9]C:R[-1]C
If InStr(1, sFrml, "C:") Then
lC = Target.Column - 1
Else
lC = Mid(sFrml, InStr(1, sFrml, "C") + 2, _
InStr(InStr(1, sFrml, "C"), sFrml, "]") - (InStr(1, sFrml, "C") + 2))
End If
lRstrt = Target.Row + Mid(sFrml, 3, InStr(1, sFrml, "]") - 3)
lRend = Target.Row + Mid(sFrml, InStr(1, sFrml, ":") + 3, _
InStr(InStr(1, sFrml, ":"), sFrml, "]") - (InStr(1, sFrml, ":") + 3))
Set rStrt = Cells(1, Target.Column + lC)
Do While Not IsNumeric(rStrt.Value) Or rStrt.Value = vbNullString
Set rStrt = rStrt.Offset(1, 0) 'go to 1st cell with value in summed range
Loop
Set rEnd = Cells(Target.Row - 1, Target.Column + lC)
If rEnd.Value = vbNullString Then
Set rEnd = rEnd.End(xlUp) 'go to last cell with value in summed range
End If
' now check to see if celles between rStrt and rEnd are included in sum formula
If rStrt < lRstrt Or rEnd > lRend Then
MsgBox "Your formula does not contain full range above"
Else
' everythin is OK
End If
End If
End If
End With
End Sub
```