AutoFormatting Style Selection
 

I use the following VBA and it works fine:

Selection.AutoFormat Format:=xlRangeAutoFormatList2, Number:=True, _
Font:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

However, I would like to have the Format style as a variable - how do I do
it. I tried this and gotten an "AutoFormat method of Range class failed"
error. My statement is:

vList = "List2"

Selection.AutoFormat Format:="xlRangeAutoFormat" & vList, Number:=True, _
Font:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

Any help is appreciated.

AutoFormatting Style Selection
 

Hi Aquarius,
The format is a number. You've attempted to use text so you get an error.
I have listed all the Formats and their appropriate (behind the scenes)
number.

In other words,
Format:=xlRangeAutoFormatList2
and
Format:=11
are the same.

xlRangeAutoFormatSimple -4154
xlRangeAutoFormatNone -4142
xlRangeAutoFormatClassic1 1
xlRangeAutoFormatClassic2 2
xlRangeAutoFormatClassic3 3
xlRangeAutoFormatAccounting1 4
xlRangeAutoFormatAccounting2 5
xlRangeAutoFormatAccounting3 6
xlRangeAutoFormatColor1 7
xlRangeAutoFormatColor2 8
xlRangeAutoFormatColor3 9
xlRangeAutoFormatList1 10
xlRangeAutoFormatList2 11
xlRangeAutoFormatList3 12
xlRangeAutoFormat3DEffects1 13
xlRangeAutoFormat3DEffects2 14
xlRangeAutoFormatLocalFormat1 15
xlRangeAutoFormatLocalFormat2 16
xlRangeAutoFormatAccounting4 17
xlRangeAutoFormatLocalFormat3 19
xlRangeAutoFormatLocalFormat4 20
xlRangeAutoFormatReport1 21
xlRangeAutoFormatReport2 22
xlRangeAutoFormatReport3 23
xlRangeAutoFormatReport4 24
xlRangeAutoFormatReport5 25
xlRangeAutoFormatReport6 26
xlRangeAutoFormatReport7 27
xlRangeAutoFormatReport8 28
xlRangeAutoFormatReport9 29
xlRangeAutoFormatReport10 30
xlRangeAutoFormatClassicPivotTable 31
xlRangeAutoFormatTable1 32
xlRangeAutoFormatTable2 33
xlRangeAutoFormatTable3 34
xlRangeAutoFormatTable4 35
xlRangeAutoFormatTable5 36
xlRangeAutoFormatTable6 37
xlRangeAutoFormatTable7 38
xlRangeAutoFormatTable8 39
xlRangeAutoFormatTable9 40
xlRangeAutoFormatTable10 41
xlRangeAutoFormatPTNone 42

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Aquaris" wrote:

I use the following VBA and it works fine:

Selection.AutoFormat Format:=xlRangeAutoFormatList2, Number:=True, _
Font:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

However, I would like to have the Format style as a variable - how do I do
it. I tried this and gotten an "AutoFormat method of Range class failed"
error. My statement is:

vList = "List2"

Selection.AutoFormat Format:="xlRangeAutoFormat" & vList, Number:=True, _
Font:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

Any help is appreciated.

AutoFormatting Style Selection
 

Hi Gary,

It solved the problem. Thank you very much.


"Gary L Brown" wrote:

Hi Aquarius,
The format is a number. You've attempted to use text so you get an error.
I have listed all the Formats and their appropriate (behind the scenes)
number.

In other words,
Format:=xlRangeAutoFormatList2
and
Format:=11
are the same.

xlRangeAutoFormatSimple -4154
xlRangeAutoFormatNone -4142
xlRangeAutoFormatClassic1 1
xlRangeAutoFormatClassic2 2
xlRangeAutoFormatClassic3 3
xlRangeAutoFormatAccounting1 4
xlRangeAutoFormatAccounting2 5
xlRangeAutoFormatAccounting3 6
xlRangeAutoFormatColor1 7
xlRangeAutoFormatColor2 8
xlRangeAutoFormatColor3 9
xlRangeAutoFormatList1 10
xlRangeAutoFormatList2 11
xlRangeAutoFormatList3 12
xlRangeAutoFormat3DEffects1 13
xlRangeAutoFormat3DEffects2 14
xlRangeAutoFormatLocalFormat1 15
xlRangeAutoFormatLocalFormat2 16
xlRangeAutoFormatAccounting4 17
xlRangeAutoFormatLocalFormat3 19
xlRangeAutoFormatLocalFormat4 20
xlRangeAutoFormatReport1 21
xlRangeAutoFormatReport2 22
xlRangeAutoFormatReport3 23
xlRangeAutoFormatReport4 24
xlRangeAutoFormatReport5 25
xlRangeAutoFormatReport6 26
xlRangeAutoFormatReport7 27
xlRangeAutoFormatReport8 28
xlRangeAutoFormatReport9 29
xlRangeAutoFormatReport10 30
xlRangeAutoFormatClassicPivotTable 31
xlRangeAutoFormatTable1 32
xlRangeAutoFormatTable2 33
xlRangeAutoFormatTable3 34
xlRangeAutoFormatTable4 35
xlRangeAutoFormatTable5 36
xlRangeAutoFormatTable6 37
xlRangeAutoFormatTable7 38
xlRangeAutoFormatTable8 39
xlRangeAutoFormatTable9 40
xlRangeAutoFormatTable10 41
xlRangeAutoFormatPTNone 42

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Aquaris" wrote:

I use the following VBA and it works fine:

Selection.AutoFormat Format:=xlRangeAutoFormatList2, Number:=True, _
Font:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

However, I would like to have the Format style as a variable - how do I do
it. I tried this and gotten an "AutoFormat method of Range class failed"
error. My statement is:

vList = "List2"

Selection.AutoFormat Format:="xlRangeAutoFormat" & vList, Number:=True, _
Font:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

Any help is appreciated.