Q:

Using worksheet_calculate when the number of rows change

I have a range of data in column x and y:

I want to compare column x to y and send out a notification whenever x > 2y. This is the code for that: (P.S. this code runs perfectly).
Private Sub Worksheet_Calculate()

If Range("A2").Value <> PrevValb Then
  PrevValb = Range("A2").Value
  If Range("A2").Value > 2 * Range("B2").Value Then
    Dim result1 As VbMsgBoxResult
    result1 = MsgBox("A2 has exceeded range", vbOKCancel, "Range Exceeded")
    If result1 = vbCancel Then
      Stop
  End If
  End If
End If

If Range("A3").Value <> PrevValc Then
  PrevValc = Range("A3").Value
  If Range("A3").Value > 2 * Range("B3").Value Then
    Dim result2 As VbMsgBoxResult
    result2 = MsgBox("A3 has exceeded range", vbOKCancel, "Range Exceeded")
    If result2 = vbCancel Then
      Stop
    End If
  End If
End If

If Range("A4").Value <> PrevVald Then
  PrevVald = Range("A4").Value
  If Range("A4").Value > 2 * Range("B4").Value Then
    Dim result3 As VbMsgBoxResult
    result3 = MsgBox("A4 has exceeded range", vbOKCancel, "Range Exceeded")
    If result3 = vbCancel Then
      Stop
    End If
  End If
End If

If Range("A5").Value <> PrevVale Then
  PrevVale = Range("A5").Value
  If Range("A5").Value > 2 * Range("B5").Value Then
    Dim result4 As VbMsgBoxResult
    result4 = MsgBox("A5 has exceeded range", vbOKCancel, "Range Exceeded")
    If result4 = vbCancel Then
      Stop
    End If
  End If
End If

End Sub

BUT the number of rows change everyday and so do the cell values (A2, A3, A4, A5). I've created a NamedRange for all the values in column x and y. Is there a way to make the cell values dynamic? I would like to use a loop with integers wherein each integer refers to the row number and the loop continues until it reaches the last row. But I'm not sure how to combine that with this existing code. 
Please let me know if you require any clarifications.

A:

You need to loop your code since the last row like this :
Private Sub Worksheet_Calculate()

Dim rangeName As String
rangeName = "NamedRange"

firstRowNamedRange = Range(rangeName).Row
lastRowNamedRange = Range(rangeName).Row + Range(rangeName).Rows.count

    For i = firstRowNamedRange To lastRowNamedRange Step 1

        If Range("A" & i).Value <> PrevValb Then
            PrevValb = Range("A" & i).Value
                If Range("A" & i).Value > 2 * Range("B2" & i).Value Then
                    Dim result1 As VbMsgBoxResult
                    result1 = MsgBox("A" & i & " has exceeded range", vbOKCancel, "Range Exceeded")
                    If result1 = vbCancel Then
                    Stop
                     End If
                End If
        End If

    Next i

End Sub

