Thread: Loop to check condition of cells

Hi,
I'm looking for assistance with some code that I've been working on but haven't been able to get it to work. Column R is a calculated cell that adds percentages across the worksheet. I would like to prevent the responders from having a percentage over 100% in column R. For example, the value in R6 is currently 100% but if the responder changed V6 to 10% then the "Grand Total" would be 105%, which I'd like to have an error message displayed when a cell R6 and below are over 100%.

There may be some additional rows added and I'd like to make sure that the code contains a variable to check every row in column R starting at row 6.

The code I've tried is below:

Private Sub Worksheet_Change(ByVal Target As Range)
' This loop repeats for a fixed number of times determined by the number of rows in the range
    Dim i As Integer
    For i = 1 To Selection.CurrentRegion.Rows.Count - 1
    ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
    ActiveCell.Offset(1, 0).Select

    If ActiveCell.Value > 1 Then
    MsgBox "The value you entered exceeds the 100% total for this process.  Please adjust."
    Range(Target.Address).Value = Null
    End If

    Next i
End Sub

Please let me know if there's any additional details that you'd need to know. Thank you in advance for taking a look at this.