Keep in mind that the variant array is only good for reading the contents of
cells, not formats.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Peter T" <peter_t@discussions wrote in message
...
Hi Tskogstrom,

Your approach of checking if formats actually need changing before
changing
is definitely faster in scenarios where there's a possibility they don't
need changing. More code of course but worthwhile.

IOW, Read cell formats is significantly faster than Write formats. In a
change event it gives the added bonus of retaining Undo if no changes to
the
interface have been made.

If you are checking multiple cells in a loop, read the entire rage to a
variant, eg vFmt. If IsNull(vFmt) or vFmt is not as required, change the
entire range in one go.

Regards,
Peter T


"tskogstrom" wrote in message
oups.com...
Hi,

On Worksheet_Change event I restore format of target cells and want to
reduce the execute time as much as possible. I have several other
restore subs than this, to restore validation, conditionformats,
interior.colors, fonts etc. and I hope I could make a difference with
usage of IF conditions everywere.

If any of you know which way is fastest, I don't need to change a lot
of code just to test this ...EXAMPLES:

Sub BordThinBlackBottom(rngForm As Range)
With rngForm.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = RGB(0, 0, 0)
End With
Exit Sub
End Sub

End Sub

...OR ...

Sub BordThinBlackBottom_If(rngForm As Range)
With rngForm.Borders(xlEdgeBottom)
if .LineStyle < xlContinuous then
.LineStyle = xlContinuous
end if

if .Weight < xlThin then
.Weight = xlThin
end if

if .Color < RGB(0, 0, 0) then
.Color = RGB(0, 0, 0)
end if

End With
End Sub



Kind regards
Tskogstrom