Macro - Format Totals?

[Glenn]

Hi,

I'm using this macro to get the totals of some columns:

Sub xxx()
Dim x As Range
Set x = Range("B65536").End(xlUp)
x.Offset(1, 6).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
x.Offset(1, 7).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
x.Offset(1, 8).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
End Sub

Is there a way to set the format of the totals to $#.## with the backround color of the cell and with a border around it?

Thanks, Glenn

 

[Glenn]

Hi again!

I've done a bit of macro recording and come up with this for ONE of the totals only!

Dim y As Range
    Set y = Range("B65536").End(xlUp)
    x.Offset(1, 6).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
    x.Offset(1, 6).NumberFormat = "$#.00"
    x.Offset(1, 6).HorizontalAlignment = xlCenter
    x.Offset(1, 6).Interior.ColorIndex = 15

    With x.Offset(1, 6).Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

    With x.Offset(1, 6).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

    With x.Offset(1, 6).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

    With x.Offset(1, 6).Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

Is there a way to simplify this please?

Cheers Glenn

 

[venkat1926]

for your first question

here is an example in vba

range("a1").numberformat="0.00"

 

[jindon]

try

Dim y As Range
    Set y = Range("B65536").End(xlUp)
    With x.Offset(1, 6)
        .FormulaR1C1 = "=SUM(R2C:R[-1]C)"
        .NumberFormat = "$#.00"
        .HorizontalAlignment = xlCenter
        .Interior.ColorIndex = 15
        With .Borders
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
    End With

 

[Glenn]

Thanks Jindon,

That is much cleaner!

Glenn