Hi Jon,

The idea is to assign the Variant with the 'single' format of the entire
range (or area if multiple areas). If the format is mixed in multiple cells
the format will return Null, otherwise the format value of all cells. If the
value is not returned as required, format the entire range (or area). The
variant won't become an array.

Regards,
Peter T

"Jon Peltier" wrote in message
...
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