CODEWITHSUNDEEP
vbaexcel
The Gootch
The Gootch

Reputation: 85

Excel Flashes When Running Macro

When running the following macro, my whole spreadsheet flashes, and I would appreciate if someone would tell me how I can make that not happen.

The macro is checking to see if column D is greyed out, if it is it makes it colored and makes column F greyed out and vice versa.

It changes the color by copying the format of 3 cells I have set up with the correct colors that are in column C.

Worksheets("SHELLY").Range("D4:F19").ClearContents

If Worksheets("SHELLY").Range("D3:D19").Interior.ColorIndex = 15 Then

    Range("C41").Select
    Selection.Copy
    Range("F3:F19").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("C42").Select
    Selection.Copy
    Range("D3:D19").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Else

    Range("C41").Select
    Selection.Copy
    Range("D3:D19").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("C43").Select
    Selection.Copy
    Range("F3:F19").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End If

Upvotes: 0

Views: 383

Answers (2)

DisplayName
DisplayName

Reputation: 13386

As Scott said, you must avoid the Select/Selection/Activate/ActiveXXX pattern and use fully qualified explicit range references

Furthermore you could refactor your code and avoid code duplication like follows:

Dim sourceCell1 As Range, targetRng1 As Range, sourceCell2 As Range, targetRng2 As Range
With Worksheets("SHELLY")
    If .Range("D3:D19").Interior.ColorIndex = 15 Then
        Set sourceCell1 = .Range("C41")
        Set targetRng1 = .Range("F3:F19")
        Set sourceCell2 = .Range("C42")
        Set targetRng2 = .Range("D3:D19")
    Else
        Set sourceCell1 = .Range("C41")
        Set targetRng1 = .Range("D3:F19")
        Set sourceCell2 = .Range("C43")
        Set targetRng2 = .Range("F3:F19")
    End If
End With

sourceCell1.Copy
targetRng1.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

sourceCell2.Copy
targetRng2.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

And if you want to totally eliminate any screen “activity” then you can enclose the above code in:

Application.ScreenUpdating = False
....(code above)
Application.ScreenUpdating = True

Upvotes: 2

chillin
chillin

Reputation: 4486

Try putting:

Application.ScreenUpdating = False before your code and Application.ScreenUpdating = True at the end.

Toggling this property tells Excel not to redraw/update the screen, as far as I know.

It might improve the speed of your code. Also, it's better to avoid using activate/select, but that wasn't your question.

Upvotes: 5

Related Questions