Excel VBA Screen Updating - Update Only One Cell

I have a macro that runs for about a minute and loads/processes a lot of data. Naturally I've turned off screen updating so that it runs faster and doesn't jump around all the time. However, I'd like to be able to turn on screen updating/force update one cell.

I can't just do something like:

Application.ScreenUpdating = True
<SET VALUE>
Ap开发者_StackOverflowplication.ScreenUpdating = False

Because when you set screen updating to true, it updates all the changes that have been made since you set it to false. How do I update just that one cell?

Thanks!

Naturally I've turned off screen updating so that it runs faster and doesn't jump around all the time.

The "doesn't jump around all the time" part makes me wonder if you are using Select and Activate a lot in your code. These are very time-consuming operations and you should be able to achieve most things without them.

Judicious use of the Range and/or Cells properties of the Worksheet in question should be a lot faster than any combination of Select and/or Activate. If you can speed up your code enough then you may be able to leave ScreenUpdating on and thus solve your original problem

Tie the cell to a textbox or other control, and let it show the value that you want to keep an eye on.

Kind of a hack, but you could print an error message when the particular cell you want to be notified about is updated or [for something less intrusive] just update the status bar:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub test_screen_updating()
    Range("A1:A10").ClearContents
    Application.ScreenUpdating = False
    For i = 1 To 10
        Range("A" & i).Value = i
        If i = 2 Then
            ' MsgBox "A2 was updated: " + CStr(Range("A2").Value)
            Application.StatusBar = "A2 updated to: " + CStr([A2])
        End If
        Sleep 500
    Next i
    Application.StatusBar = False
    Application.ScreenUpdating = True
End Sub

Just make sure you reset the status bar if you update it.

If speed is an issue, can you do:

Application.Calculation = xlCalculationManual
<process>
Application.Calculation =xlCalculationAutomatic

? That can make a big difference.