Please advice. Or was the primary focus with that sentence to change
whole range in one go?

Exactly that, read and assign the format to a variant then check the
contents of the variant. If multiple areas are involved do area by area, eg

For each ra in EntireRange.Areas
b = false
vFmt = ra.SomeFormat
if IsNull(vFmt) then
b = true
elseif vFmt < whatever-value then
b = true
end if
If b then
ra.SomeFormat = whatever-value
end if
' do other formats as above
Next

Be aware with borders you'll need to cater for 'If' multiple rows/columns in
an area exist and look at inside horizontal/vertical borders and edges as
appropriate. You can loop borders 7 to 12 (be careful with 11 & 12 if
insides don't exist).

Regards
Peter T

"tskogstrom" wrote in message
oups.com...
Great, then I'll start to change the code. As you saw I have a
foundation of code like "BordThinBlackBottom" that are used in all
sheets needed.

I dind't follow your suggestion of lopping cells and using dim
variant? I use to count if there are more than one area and after that
use "application.intersect(target, rgn1, rng2) is nothing" to drill
down to optimum ranges to restore. If cells.count is one, I act in
another way to faster drill down to needed actions. I use Target as
range and start with checking if range(target) is nothing etc. Do I
miss something without Variant approach?

Please advice. Or was the primary focus with that sentence to change
whole range in one go?

/Regards
Tskogstrom

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

- Visa citerad text -