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

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

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 -

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

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 -

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

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 -

Comments in line -

Ok. So this variant approach will be best if there are few formats and
several ranges/areas. Right?

You only need the variant approach if you are reading formats of more than
one cell at a time, to cater for the possibility of mixed formats of the
same type. It doesn't relate to how many types formats you need to check.
Each will need to be done individually unless you go for a pasteformats
approach.

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 - ?

As above, you need to check each format individually, but you can process
the entire range (or area) in one go as I suggested previously.

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

Yes

Typically in the change event only one cell is involved, sometimes a block
if user (say) pastes cells. Potentially though user can also change multiple
areas at the same time. So your intersect could return a single cell, one or
multiple areas each with one or multiple cells. However maybe you only need
to know if the changed cell intersects with your particular 'big range
maintain format', If it does process the 'big range' just in case anything
has been missed.

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.

Sure, pass the range to be processed to a routine in a normal module.

... 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.

Why must the count be two? could be any number surely. Though you would need
to know if the area is a single cell, single/multiple rows/columns to cater
for inside borders.

BTW just in case you are not aware, user changed formats generally don't
trigger a change event though there are some exceptions.

Regards,
Peter T


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 -

First I said Huh?, but then I figured out what you meant. Check the top left
cell of the range, and based on that cell, decide whether to change all the
formats. In some cases this might be fine, but I'd be afraid of running into
differently formatted borders, fills, or text in the top row or left column
of a table.

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


"Peter T" <peter_t@discussions wrote in message
...
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

Check the top left cell of the range, and based on that cell, decide
whether to
change all the formats.

No not like that at all

Sub test()
Dim b as Boolean, v
[a1] = 1: [j10000] = 1
Range("B2,B8:G12,C15:I20").Interior.ColorIndex = 5
Stop ' look at sheet, press F5
b = False
With ActiveSheet.UsedRange
v = .Interior.ColorIndex
If IsNull(v) Then
b = True
ElseIf v < 6 Then
b = True
End If
If b Then
..Interior.ColorIndex = 6
End If
End With
End Sub

I might be missing what you are saying but the above is what I had in mind
for the OP. Ie, only write formats if any or all are not as required in the
entire range (or area), no need to loop each cell. No problem with mixed
formats.

Regards,
Peter T

"Jon Peltier" wrote in message
...
First I said Huh?, but then I figured out what you meant. Check the top
left
cell of the range, and based on that cell, decide whether to change all
the
formats. In some cases this might be fine, but I'd be afraid of running
into
differently formatted borders, fills, or text in the top row or left
column
of a table.

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


"Peter T" <peter_t@discussions wrote in message
...
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

In that crude example the "b = False" is not appropriately placed. In a loop
or before checking further format types ensure it's reset to false.

Forgot to mention, in very large areas it might be worth breaking down into
smaller sub blocks for the sake of overall speed, but a bit more code.

Peter T

"Peter T" <peter_t@discussions wrote in message
...
Check the top left cell of the range, and based on that cell, decide
whether to
change all the formats.

No not like that at all

Sub test()
Dim b as Boolean, v
[a1] = 1: [j10000] = 1
Range("B2,B8:G12,C15:I20").Interior.ColorIndex = 5
Stop ' look at sheet, press F5
b = False
With ActiveSheet.UsedRange
v = .Interior.ColorIndex
If IsNull(v) Then
b = True
ElseIf v < 6 Then
b = True
End If
If b Then
.Interior.ColorIndex = 6
End If
End With
End Sub

I might be missing what you are saying but the above is what I had in mind
for the OP. Ie, only write formats if any or all are not as required in
the
entire range (or area), no need to loop each cell. No problem with mixed
formats.

Regards,
Peter T

"Jon Peltier" wrote in message
...
First I said Huh?, but then I figured out what you meant. Check the top
left
cell of the range, and based on that cell, decide whether to change all
the
formats. In some cases this might be fine, but I'd be afraid of running
into
differently formatted borders, fills, or text in the top row or left
column
of a table.

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


"Peter T" <peter_t@discussions wrote in message
...
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

OK, now I understand, maybe ... ,

I tried this but didn't made it work. I used:

With rngForm.Borders(xlEdgeTop)
If .LineStyle < xlContinuous Then
.LineStyle = xlContinuous
End If
If .Weight < xlMedium Then
.Weight = xlMedium
End If
If .Color < RGB(255, 0, 0) Then
.Color = RGB(255, 0, 0)
End If
End With

.... but if rng had several border ranges, it passed by. But you use
IsNull(). That might make a different.

Kind regards
Tskogstrom

But you use IsNull(). That might make a different.

Absolutely, when checking for the possibility of mixed formats in a
multicell range

You could do this -

If isNull(.LineStyle) Then
.LineStyle = xlContinuous
Elseif < xlContinuous then
.LineStyle = xlContinuous
End If

But you asked for speed, it's probably faster to assign the format value
(possibly Null) to a variant first to avoid reading the format twice as I
suggested. Reading cell formats is relatively slow though not as slow as
applying formats (avoid applying altogether if they don't need changing).

Regards,
Peter T

"tskogstrom" wrote in message
ps.com...
OK, now I understand, maybe ... ,

I tried this but didn't made it work. I used:

With rngForm.Borders(xlEdgeTop)
If .LineStyle < xlContinuous Then
.LineStyle = xlContinuous
End If
If .Weight < xlMedium Then
.Weight = xlMedium
End If
If .Color < RGB(255, 0, 0) Then
.Color = RGB(255, 0, 0)
End If
End With

... but if rng had several border ranges, it passed by. But you use
IsNull(). That might make a different.

Kind regards
Tskogstrom