sum column vba

[billandrew]

This is an additional question from a previous post.

I received the following code, however when using in a different worksheet it is not working well. I am trying to place a total row (sum) and format this row. Possibly bold font & fill


The range is D5:Lxx). Column 12 may expand.

Dim lr As Long

For i = 4 To 12
lr = Cells(Rows.Count, i).End(xlUp).Row
If lr > 4 Then
Cells(lr + 1, i) = WorksheetFunction.Sum(Range(Cells(5, i), Cells(lr, i)))

Next i

 

[Joe4]

Then do it like this (note that we know need to get rid of our underscore and use an END IF since we have multiple commands being returned in our IF ... THEN block):

For i = 4 To 12
    lr = Cells(Rows.Count, i).End(xlUp).Row
    If lr > 4 Then _
        Cells(lr + 1, i) = WorksheetFunction.Sum(Range(Cells(5, i), Cells(lr, i)))
        Cells(lr + 1, i).Font.Bold = True
        Cells(lr + 1, i).Interior.Color = 65535
Next i

If you would like to use some color other than yellow, just turn on your Macro Recorder, record yourself filling a cell with the color you want, stop the Macro Recorder and view your recorded code to see what that color code is, and then make the change to the code above.

 

[billandrew]

absolutely awesome.

With no doubt you have thought me to fish, at least for today.

Thank you Joe4!!

 

[Joe4]

You are welcome!