Macro suddenly causes the screen to blink

  • Thread starter Thread starter Top Spin
  • Start date Start date
T

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
 
It's customary to copy/paste your code here for comments but try
application.screenupdating=false
code
application.screenupdating=true
 
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

Application.ScreenUpdating = False

'your code that moves things

Application.ScreenUpdating = True
Click to expand...

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.
Click to expand...

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
 
why not try just
with range(yourrange).font
..ColorIndex = 0
..Bold = False
End With
 
why not try just
with range(yourrange).font
.ColorIndex = 0
.Bold = False
End With
Click to expand...

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
 
why not try just
with range(yourrange).font
.ColorIndex = 0
.Bold = False
End With
Click to expand...

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
 
Back
Top