Please help! formatting last row

[Robotacha2010]

Can someone please help me with the next peice of code required for this:

I want to go to the last row of the sheet and add some BOLD and Colour formatting.

the TOTALS range is C26:X26 (although the amount of rows change on a daily basis)

I have all the code working, and have got as far as:

 With Sheets("Sheet1")
             .Range("C" & .Rows.Count).End(xlUp).Select
              ActiveCell.Offset(0, -1) = "Turnover"
    
           End With

Which adds "Turnover" into cell B**, I then want to format the subtotals to BOLD and highlight the cells as GREY.


Can anyone help please?


thankyou.

[Domski]

Like this:

With Sheets("Sheet1")
    With .Range("C" & .Rows.Count).End(xlUp)
        .Offset(0, -1) = "Turnover"
        With .Resize(1, 22)
            .Font.Bold = True
            .Interior.ColorIndex = 15
        End With
    End With
End With

Dom

[Robotacha2010]

thankyou greatly.

Can you just answer 1 more question for me please?

where you have

.resize(1,22)

where 22 refers to the column (X), what is the '1' doing?

[Domski]

This refers to the range C26:

With .Range("C" & .Rows.Count).End(xlUp)

This resizes the range being referred to 1 row tall (same as it already is) and 22 columns wide (across to X):

.resize(1,22)

Dom

[Robotacha2010]

thankyou.

hate askign questions, and I am learning from everything Im told,

Could you possibly add a little more code for me?

I want these newly formatted cells, to also have borders?

I have tried:

With Sheets("Sheet1")
     With .Range("C" & .Rows.Count).End(xlUp)
        .Offset(0, -1) = "Turnover"
        .Font.Bold = True
             With .Resize(1, 22)
             .Font.Bold = True
             .Interior.ColorIndex = 15
                With Selection.Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Weight = xlMedium
                    .ColorIndex = xlAutomatic
               End With
             End With
         End With
      End With

[Domski]

You were nearly there, just didn't need the selection bit:

With Sheets("Sheet1")
     With .Range("C" & .Rows.Count).End(xlUp)
        .Offset(0, -1) = "Turnover"
        .Font.Bold = True
             With .Resize(1, 22)
             .Font.Bold = True
             .Interior.ColorIndex = 15
                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Weight = xlMedium
                    .ColorIndex = xlAutomatic
                End With
             End With
    End With
End With

Try and get using Select and Activate out of your head when writing vba code. They are the manual actions that you perform when working on a spreadsheet and as such if you use the macro recorder it translates them into code but they are not needed and best avoided.

Dom

[Robotacha2010]

Again, sincere thanks!

I can now add this macro button to a colleagues computer, schedule the raw data speradsheet to be emailed to him, and he can do the work himself! now saving me 15 minutes each day :D

all thanks to you.

regards,

"a happy" Michael.