Everything so far has worked one cell at a time — fine for five orders, painfully slow for fifty thousand. The fix is to stop touching the worksheet cell-by-cell and instead move the whole block into an array in memory, work on it there, and write it back in one shot.

Reading a Range into an Array

Dim dataArr As Variant
dataArr = ws.Range("A2:I6").Value    ' one read, not 45 individual reads

dataArr is now a 2D array in memory: dataArr(1,1) is ORD1001, dataArr(1,4) is "Laptop Stand", and so on — VBA arrays read from a range are 1-based, not 0-based, which is a common trip-up.

Processing the Array

Happens with ordinary loops — but now you're looping over memory, which is thousands of times faster than looping over the worksheet:

Dim i As Long
Dim recalculated As Double
 
For i = 1 To UBound(dataArr, 1)
    recalculated = dataArr(i, 5) * dataArr(i, 6) * (1 - dataArr(i, 7))
    If Abs(recalculated - dataArr(i, 8)) > 0.01 Then
        Debug.Print "Mismatch on row " & i & ": sheet says " & dataArr(i, 8) & _
                    ", recalculated " & Format(recalculated, "0.00")
    End If
Next i

Writing the Array Back

Again a single operation:

ws.Range("A2:I6").Value = dataArr

Avoiding Select and Activate

Recorded macros lean on these heavily (Range("A1").Select then Selection.Font.Bold = True), but they're unnecessary and slow — every .Select forces Excel to redraw the screen. Reference the range or object directly instead:

' Avoid:
ws.Range("A1").Select
Selection.Font.Bold = True
 
' Prefer:
ws.Range("A1").Font.Bold = True

Performance Considerations

These matter once your data grows past a few hundred rows:

Application.ScreenUpdating = False   ' stop redrawing while the macro runs
Application.Calculation = xlCalculationManual   ' pause recalculation
Application.EnableEvents = False     ' suppress other macros triggering mid-run
 
' ... your fast array-based code here ...
 
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

Always restore these settings at the end — and wrap the restoration in error handling so a mid-run crash doesn't leave Excel stuck with screen updating turned off.

Worked Example: the Order Integrity Checker

This chapter has been building toward this:

Option Explicit
 
Sub VerifyOrderTotals()
    Dim ws As Worksheet
    Dim dataArr As Variant
    Dim lastRow As Long
    Dim i As Long
    Dim recalculated As Double
    Dim issues As String
 
    Set ws = ThisWorkbook.Worksheets("Orders")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
 
    Application.ScreenUpdating = False
 
    dataArr = ws.Range("A2:I" & lastRow).Value
    issues = ""
 
    For i = 1 To UBound(dataArr, 1)
        recalculated = dataArr(i, 5) * dataArr(i, 6) * (1 - dataArr(i, 7))
        If Abs(recalculated - dataArr(i, 8)) > 0.01 Then
            issues = issues & dataArr(i, 1) & ": sheet=" & dataArr(i, 8) & _
                      ", expected=" & Format(recalculated, "0.00") & vbNewLine
        End If
    Next i
 
    Application.ScreenUpdating = True
 
    If issues = "" Then
        MsgBox "All " & UBound(dataArr, 1) & " order totals check out."
    Else
        MsgBox "Discrepancies found:" & vbNewLine & issues
    End If
End Sub

Run this against the sample table and it should report all five totals as correct — try changing ORD1002's Total on the sheet to something wrong and re-run to see the alert fire.

Task

1. Copy VerifyOrderTotals into your workbook and run it against the five sample orders.
2. Manually break one order's Total on the sheet (type in an obviously wrong number) and re-run — confirm the mismatch is reported with the correct Order ID.
3. Add ten more rows of made-up order data below row 6, and confirm the macro still works without any code changes — this is lastRow and array-processing paying off.