Code duration inconsistent

[cplindem]

I have a large subroutine that formats Excel workbooks. The entire subroutine usually takes ~30 seconds to run on 40,000 rows of data. However, it sometimes decides to take ~3 minutes to run. It seems completely random to me. I tried to isolate a snippet of code that might be causing the variance in duration, by using "Stop". The culprit seems to be the loop in this section:

Dim FoundCell As Range
    Dim FirstAddr As String
    Dim LastCell As Range
    Dim LastRow As Long
    
    LastRow = Range("A1048576").End(xlUp).Row
    
    With Range("A1:A" & LastRow)
        Set LastCell = .Cells(LastRow)
    End With
        
    Set FoundCell = Range("A1:A" & LastRow).Find(what:="Total:", After:=LastCell, MatchCase:=True, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    
    If Not FoundCell Is Nothing Then
        FirstAddr = FoundCell.Address
    End If
    
    Stop '(lots of code comes before this). This is the loop that sometimes takes forever to run through:
    
    Do Until FoundCell Is Nothing
        With FoundCell
            With .EntireRow
                .Font.Size = 8
                .Font.color = Cells(1, 8).Interior.color
                .NumberFormat = "#,##0"
            End With
            .Offset(0, 1).NumberFormat = """n=""#,##0"
        End With
        Set FoundCell = Range("A1:A" & LastRow).FindNext(After:=FoundCell)
        If FoundCell.Address = FirstAddr Then
            Exit Do
        End If
    Loop
    
    Stop '(lots of code comes after this)

Any ideas why this section might run quickly sometimes, slowly at others? This variance occurs without me changing anything in the code. The data that I am testing the code on also never changes.

[SamT]

See if this runs faster on the slow worksheets

Do Until FoundCell Is Nothing
     'Optimized for speed
        With FoundCell.EntireRow
          NewColor = .Cells(8).Color '<--- Be sure to Dim NewColor above
            With .Font
            .Size = 8
            .Color = NewColor
            End With
            .NumberFormat = "#,##0"
        End With
        FoundCell.Offset(0, 1).NumberFormat = """n=""#,##0"
    End With
Set FoundCell = ...

[cplindem]

Thank you. With a couple modifications (you had an extra 'End With' and you missed '.Interior' before the color) that did shave a few seconds off the total duration.

However, the issue isn't really the total duration per se. The issue is the total duration varies. I don't mean to say that it varies from worksheet to worksheet. I have been testing it on the exact same worksheet. Sometimes the total duration is ~30 seconds, sometimes a few minutes. ...on the same worksheet. Basically I'm changing nothing, but the duration will randomly go on for a few extra minutes longer for seemingly no reason.

[GTO]

If you could substitute same data types for any sensitive data and post the wb, so we could see what is really in action, that might be helpful.

[cplindem]

I don't have anymore time today, but perhaps on Monday.

Thanks all