Flickering Cells when I run this code

[madhatter40]

I have included the following code. When I run the following code it flickers the selected cells for about 5 seconds and complete the job. I want to know why do the cells flicker for about 5 seconds? The code does what it is suppose to do, just don't like the flickering. What if anything can I do to stop the flickering?



Sub Contents()
'This selects the cells to color Green
Range("C8").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 52377
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' This selects the cells to turn Gray
Range("C9:C15,B11:B13").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
' This selects the cells to clear formulas in
Worksheets("Scrap Calculator").Range("B9,B10,B14,B15").ClearContents
End Sub

Thanks so much

[Spencer101]

Add Application.ScreenUpdating = False to the start of the code and Application.ScreenUpdating = True to the end of the code.

[HaHoBe]

Hi, madhatter40,

please use the code tags to dipslay the coding.

Maybe itīs due to calculation? You could try and set the ScreenUpdating of for the macro:

Please Login or Register  to view this content.

Code ran in nearly no time - no formulas included in workbook for testing here.

Ciao,
Holger

[madhatter40]

So I updated the code and it does run in about 3 seconds. It still flickers for the 3 seconds. Is it suppose to do that? I was hoping for it to just run with no flickering.

[HaHoBe]

Hi, madhatter40,

which code did you use: the one you supplied or the modified? If itīs yours I assume that there are events which may get fired due to change of selections. As long as you canīt provide a sample workbook I think anybody will have a hard time guessing at what may be the reason for the flickering (I already have used two of my possible three guesses here ).

Ciao,
Holger

[madhatter40]

I will include all the code that goes with this worksheet.
The following is what fires when a user selects from a dynamic drop down list:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("G2")

Please Login or Register  to view this content.

This is the code I modified:

Please Login or Register  to view this content.

screen shot.jpg

I hope this helps. The upper right corner is where the user selects from the drop down menu. (Where is says "Machine PL048")

[martindwilson]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[madhatter40]

I hvae figured it out. I switched the "Application.ScreenUpdating = False" to the following:

Application.EnableEvents = True

OLD Code

Please Login or Register  to view this content.

New Code:

Please Login or Register  to view this content.

Thanks everyone for the help