Universal solution for Excel 2013 & 2016 screen flicker due to ScreenUpdating = FALSE not working

Question

There have been dozens of questions about screen flickering in Excel 2013 and 2016, as Application.ScreenUpdating = False does not work as it used to.

Screen Flickering VBA Excel
Excel ScreenUpdating False and still flickering screen
ScreenUpdating = False fails in Excel 2013 and 2016

One partial solution involves disabling worksheet calculations, but they can be necessary in some models. Another partial solution involves making workbooks invisible, which is either confusing for the user (if all workbooks disappear), or still sees the screen flickering (if you make all workbooks except one disappear).

Ideally, I am looking for a solution that allows DoEvents to be run (so that it doesn't look like Excel is hanged), the allows the StatusBar to be visible (for reporting on progress), and prevents all other screen flicker.

Why can't Microsoft just sort this out :(

Here is some sample code which requires two blank workbooks (Book1 and Book2) and induces a horrible screen flicker.

Sub Macro1()
    ' this worked fine in Excel 2010
    Application.ScreenUpdating = False

    ' this clears all flicker, but the whole of excel disappears
    ' Application.Visible = False
    ' this changes the flicker between Book1 and blank white screen
    ' Application.Workbooks("Book2").Windows(1).Visible = False

    ' some flickery code
    For i = 1 To 10
        Windows("Book2").Activate
        Call Jiggle("Red")
        Windows("Book1").Activate
        Call Jiggle("Yel")
    Next i

    ' restore normality
    Application.ScreenUpdating = True
    Application.Visible = True
End Sub


Sub Jiggle(c As Variant)
    For i = 1 To 100
        ActiveCell.Offset(1, 0).Select
        If (c = "Yel") Then Selection.Interior.Color = 255
        If (c = "Red") Then Selection.Interior.Color = 65535
    Next i
End Sub

Answer 1

it seems to me that your whole code could boil down to:

Public Sub Macro1()
    Application.ScreenUpdating = False 
    Workbooks("Cartel2").Activate
    ActiveCell.Offset(1).Resize(1000).Interior.Color = 65535
    Workbooks("Cartel3").Activate
    ActiveCell.Offset(1).Resize(1000).Interior.Color = 255
    Application.ScreenUpdating = True
End Sub

Answer 2

The flickering comes most likely from using .Select and .Activate:

It's the environment screen that's updating to reflect the activation of the respective workbooks. Application.ScreenUpdating applies only to the application instance, it doesn't disable your entire monitor from refreshing as the various Workbook windows gain/lose focus from the Activate method. As you'll observe, while the screen flickers, you don't see the cell color changes until the procedure has finished. This suggests that Application.ScreenUpdating is working exactly as its intended to work, and that to resolve this, you should read and apply How to avoid using Select in Excel VBA.

Using .Activate or .Select is only necessary in very rare cases, so avoid it where ever possible.

I'd also note that if it is a strict requirement to rely on the Selection or ActiveCell as user input, it won't be possible to fully eliminate the window flickering, because Selection or ActiveCell only exist within scope of the ActiveSheet in the ActiveWorkbook.

I recommend to give the worksheet as parameter in Jiggle and then to address the cells absolutely. This makes your code more reliable and it runs in the background without selecting and without activating anything.

Option Explicit 

Public Sub Macro1()
    Application.ScreenUpdating = False

    ' some flickery code
    Dim i As Long
    For i = 1 To 10
        Call Jiggle("Red", Workbooks("Book2").Worksheets(1))
        Call Jiggle("Yel", Workbooks("Book1").Worksheets(1))
    Next i

    ' restore normality
    Application.ScreenUpdating = True
End Sub

Public Sub Jiggle(c As Variant, ws As Worksheet)
    Dim i As Long
    For i = 1 To 100
        With ws.Cells(i, 1) 'address your cells absolutely
            Select Case c
                Case "Yel":
                    .Interior.Color = 255
                Case "Red":
                    .Interior.Color = 65535
            End Select
        End With
    Next i
End Sub

Note that this example is to show how not to use .Select and @DisplayName showed a shorter/faster way to achieve the same.