Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I want to create a custom currency number format only in the total row table if there is a solution and which I mark the yellow color in the screenshot.

Thanks

Sub test2()
Dim objListObj As ListObject
Set objListObj = Sheets("test").ListObjects(1)
objListObj.ShowTotals = True
With Sheets("test").ListObjects("Table1")
.ListColumns("Total").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("Pot. :").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("Total End :").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("PRICE2").TotalsCalculation = xlTotalsCalculationSum
End With
End Sub
    Headers = Array("Total", "Pot. :", "Total End :", "PRICE2")
    With ThisWorkbook.Worksheets("test").ListObjects("Table1")
        .ShowTotals = True
        Dim n As Long
        For n = LBound(Headers) To UBound(Headers)
            With .ListColumns(Headers(n))
                .TotalsCalculation = xlTotalsCalculationSum
                .Parent.TotalsRowRange.Columns(.Index) _
                    .NumberFormat = "#,##0.00 $"
            End With
        Next n
    End With
End Sub
                Thank you for your reply. It works perfectly and one more how to add the word in column A in the total row with the word "Grand Total"
– roy
                Feb 25, 2022 at 12:55
I recorded a macro and ended up with something like this:
Selection.NumberFormat = "#,###,##0.0000000000000 [$?-x-xbt1]"
How did I find this format? Well, I clicked right in a cell, asked for "Format cells", I chose "Number", "Currency", I chose the one which resembled the most to what I wanted, and clicked on "Custom" in order to see the actual formatting rule, which I can modify afterwards, as in this example:
(The formatting rule, you can see in the "Type" field, gets filled in automatically.)
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.