Optimizing Code

Hi,

I have a worksheet where I calculate the Z score of around 4 million cells. To speed it up I have a column of all row averages and standard deviations (ThisWorkbook.numofcolumns+2 and +3). It is still really slow so I put a loading screen. The loading screen is updated by the variable Percent. I know when setting formulas that I should do the full range at once for speed but I could not figure out how to update the loading screen at the same time (the only thing I could think of was a modeless userform offscreen with a timer). I was wondering if someone could help me a) speed it up and/or b) update the variable Percent while it calculates the Z score. Thanks!

With ThisWorkbook.Worksheets("Z score")
    
    For i = 2 To ThisWorkbook.numofcolumns + 1

    .Range(.Cells(2, i), .Cells(ThisWorkbook.numofrows + 1, i)).FormulaR1C1 = "=(('Gene Expression'!RC-'Gene Expression'!RC" & ThisWorkbook.numofcolumns + 2 & ")/'Gene Expression'!RC" & ThisWorkbook.numofcolumns + 3
  'Update loading screen
    Percent = (i + 50) / (ThisWorkbook.numofcolumns + 200)
    With Progress
            .FrameProgress.Caption = Format(Percent, "0%")
            .LabelProgress.Width = Percent * (.FrameProgress.Width - 10)
        End With

    DoEvents
    
    Next i