vba applying format for table column

Question

I am trying to apply a currency format to a specific column in a table. I am trying 2 options:

Sheet1.ListObjects("MyTaqble").ListColumns("Balance").Style = "Currency"

And:

Sheet1.ListObjects("MyTaqble").ListColumns("Balance").NumberFormat = _
    "_([$€-x-euro2] * #,##0.00_);_([$€-x-euro2] * (#,##0.00);_([$€-x-euro2] * ""-""??_);_(@_)"

For both I get the following error: Object doesn't support this property or method

What is the right way to apply format to a table column?

Answer 1

As far as I understood you would like to do something like that

Sub FormatIt()
Dim v As ListColumn
    Set v = Sheet1.ListObjects("MyTaqble").ListColumns("Balance")
    v.Range.Style = "Currency"
End Sub

or

Sub FormatItA()
Dim v As ListColumn
    Set v = Sheet1.ListObjects("MyTaqble").ListColumns("Balance")
    v.Range.NumberFormat = _
    "_([$€-x-euro2] * #,##0.00_);_([$€-x-euro2] * (#,##0.00);_([$€-x-euro2] * ""-""??_);_(@_)"
End Sub

Answer 2

You're close. You need to apply the style (or numberformat) to the Range property of the ListColumns object.

So:

Sheet1.ListObjects("Table1").ListColumns("Balance").Range.Style = "Currency"