Ok. So this variant approach will be best if there are few formats and
several ranges/areas. Right?
But if I should check top, left, bottom, right, edgetop,
edgeleft,edgebottom, edgeright, different colors of internal color,
validation, several different colors of bords, bords thin and medium,
conditionalformatting etc. I guess it is better with my current
approach - ?

1/ check if one- or more areas intersect with target
2/ format each area with all cells with same formats in one go
3/ format each area that need further formats

Restoring subs are called like sub BordThinBlackBottom above to make
it easy arrange it on the different sheets with different content -
but with similar base of colors, lines etc.

.... And keeping in mind inside horizontal/vertical borders and
Border(xlEdgeLeft/Right/Bottom/Top) demands of at least a count of two
cells as row or column.

Regards
Tskogstrom


On 5 Apr, 14:18, "Peter T" <peter_t@discussions wrote:
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
roups.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- Dölj citerad text -

- Visa citerad text -