AutoFormatting Style Selection

[Aquaris]

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.

[Gary L Brown]

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
gary_brown@ge_NOSPAM.com
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.

[Aquaris]

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
> gary_brown@ge_NOSPAM.com
> 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.