Macro suddenly causes the screen to blink

[Top Spin]

I wrote a little macro to scan various columns in a spreadsheet and
highlight selected cells by changing the color or making them bold.

The macro first loops through each column setting each cell to black
and not bold. At the same time, it examines each value and remembers
the highest and lowest value.

Finally, it changes the max/min cell in each column to bold and either
red or green.

I have been using this macro for several weeks. It has worked
perfectly.

Today, I added some new code, mainly having to do with a new column --
similar processing as with the other columns. For some reason, the
spreadsheet screen "blinks" or "flashes" after almost every macro
operation -- at least after each "Range" command. Prior to today, I
would see the macro scanning the columns because the selection
rectangle would move quickly across the sheet.

The new code is not much different from the old, but now the macro
takes 20 tiems longer to run because of the screen flashing.

Can anyone suggest what might be causing this behavior?

Thanks

 

[Don Guillett]

It's customary to copy/paste your code here for comments but try
application.screenupdating=false
code
application.screenupdating=true

 

[Harald Staff]

Hi

Impossible to way without seeing your code.
But code almost never need to select anything, so it can be shortened to run
far mode efficient and noiseless than it is now, and I bet the problems go
away with that modification too. See
http://www.dicks-blog.com/archives/2004/04/27/beginning-vba-select-and-activate

HTH. Best wishes Harald

 

[Gord Dibben]

Top

Application.ScreenUpdating = False

'your code that moves things

Application.ScreenUpdating = True

Also note that "selecting" things(which causes the flashing around)is usually
not necessary.

Range(Range("A1"), Range("A1").End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.Paste

is equal to.....

Range(Range("A1"), Range("A1").End(xlDown)).Copy _
Destination:=Sheets("Sheet2").Range("A1")

and will not cause flashing because no ranges or sheets are actually selected.

Gord Dibben Excel MVP

 

[Top Spin]

Top

Application.ScreenUpdating = False

'your code that moves things

Application.ScreenUpdating = True

That made a huge difference -- thanks.

Also note that "selecting" things(which causes the flashing around)is usually
not necessary.

Range(Range("A1"), Range("A1").End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.Paste

is equal to.....

Range(Range("A1"), Range("A1").End(xlDown)).Copy _
Destination:=Sheets("Sheet2").Range("A1")

and will not cause flashing because no ranges or sheets are actually selected.

I don't understand this syntax and cannot find it in the help. Is this
a command? a property? ???

What is the ":=" syntax? Is it documented anywhere?

Here is a snippet of code. It's a loop to reset the color and bold
attributes of a bunch of cells and to examine the values. Is there a
way to do this without selecting the cells?

Thanks



For iRow = iRowPC1 To iRowPC2 'Loop through all cells
Range(sColTlyLet & iRow).Select 'Position at next tally cell
With ActiveCell
.Font.ColorIndex = iColorAuto 'Reset it to black
.Font.Bold = False '.& make it unbold
End With
Range(sColSumLet & iRow).Select 'Position at next sum cell
With ActiveCell
.Font.ColorIndex = iColorAuto 'Reset it to black
.Font.Bold = False '.& make it unbold
nSum(iRow) = .Value 'Save the value for comparison
End With
Range(sColPCDLet & iRow).Select 'Position at % next cell
With ActiveCell
.Font.ColorIndex = iColorAuto 'Reset it to black
.Font.Bold = False '.& make it unbold
nPCD(iRow) = .Value 'Save the value for comparison
Next iRow

 

[Don Guillett]

why not try just
with range(yourrange).font
..ColorIndex = 0
..Bold = False
End With

 

[Top Spin]

why not try just
with range(yourrange).font
.ColorIndex = 0
.Bold = False
End With

I'll try that. My code was obtained by modifying code from recording a
macro.

And "yourrange" can be any rectangular range, right?

That will work for resetting all of the cells to the same value, but I
also need to examine each cell value. Actually, store it in an array.
Is there a way to store an entire range (column) in consecutive
elements of an array with a single statement?

Thanks

 

[Top Spin]

why not try just
with range(yourrange).font
.ColorIndex = 0
.Bold = False
End With

Thanks to everyone for the help. The macro now works almost
instantaneously. Even if I leave screen updating on, it only takes a
fraction of the time it used to take. Sweet!

Is there a way to assign the values in a range of cells to an array in
a single statement? If that is possible, I could eliminate all loops.

Thanks