Remove screen flicker during macro

Does anyone please know how to remove screen flicker while a macro is running in Excel 2007?
ric1942Asked:
Who is Participating?
 
Chris BottomleySoftware Quality Lead EngineerCommented:
As below, though I am concerned you have the error as there doesn't seem to be any reason for it in the code.

Chris
Sub Emerald()
'
' Emerald Macro
' Returns staff due Emerald award
'
' Keyboard Shortcut: Ctrl+e
'
    application.ScreenUpdating = false
        Range("I1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$M$100").AutoFilter Field:=9, Criteria1:=RGB(0, 0, _
        0), Operator:=xlFilterCellColor
    
    With ActiveSheet.AutoFilter.Filters(9).Criteria1
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 90
        .Gradient.ColorStops.Clear
    End With
    With ActiveSheet.AutoFilter.Filters(9).Criteria1.Gradient.ColorStops.Add(0)
        .Color = 16777215
        .TintAndShade = 0
    End With
    With ActiveSheet.AutoFilter.Filters(9).Criteria1.Gradient.ColorStops.Add(1)
        .Color = 11044088
        .TintAndShade = 0
    End With
    application.ScreenUpdating = true
    End Sub

Open in new window

0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Hello ric1942,

Set application.ScreenUpdating = false
and on completion set
application.ScreenUpdating = true

Regards,
Chris
0
 
Brian WithunCommented:
Every time you set application.screenupdating to true, it redraws.

Try not to turn it off too frequently, or use this


if not Application.ScreenUpdating then Applications.ScreenUpdating = true

Open in new window

0
 
ric1942Author Commented:
Thank you both for your answers. Can you show me where to place the code please as I keep getting a compilation error. Code attached.
Regards, Ric.

Sub Emerald()
'
' Emerald Macro
' Returns staff due Emerald award
'
' Keyboard Shortcut: Ctrl+e
'
        Range("I1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$M$100").AutoFilter Field:=9, Criteria1:=RGB(0, 0, _
        0), Operator:=xlFilterCellColor
    
    With ActiveSheet.AutoFilter.Filters(9).Criteria1
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 90
        .Gradient.ColorStops.Clear
    End With
    With ActiveSheet.AutoFilter.Filters(9).Criteria1.Gradient.ColorStops.Add(0)
        .Color = 16777215
        .TintAndShade = 0
    End With
    With ActiveSheet.AutoFilter.Filters(9).Criteria1.Gradient.ColorStops.Add(1)
        .Color = 11044088
        .TintAndShade = 0
    End With
    End Sub

Open in new window

0
 
ric1942Author Commented:
Fantastic Chris. It works really well. Thank You VERY much. Regards, Ric.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.