How can I format the last row !

[Ro477]

I am using Excel2002sp3 on WinXP. I have written

Worksheets("Report(metArb)").Activate
Range("B3").Select
Selection.End(xlDown).Select
lastfilledrow = ActiveCell.Row
ActiveSheet.Cells(lastfilledrow + 2, 5).Value = "Totaal"
sumcellformula = "=sum(h2:h" & lastfilledrow & ")"
ActiveSheet.Cells(lastfilledrow + 2, 8).Formula = sumcellformula
sumcellformula = "=sum(i2:i" & lastfilledrow & ")"
ActiveSheet.Cells(lastfilledrow + 2, 9).Formula = sumcellformula
sumcellformula = "=sum(j2:j" & lastfilledrow & ")"
End Sub

to find the last row and make some totals. This seems to work okay, but I am
having trouble selecting the lastfilledrow+2 ... that is selecting the whole
row. I'm sure it is simple, but I can't for the love of me figure out how to
do it. Basically .... how can I format the lastfilledrow+2, columns 5 to 9,
so that they are bold and red.... or basically how can I select the whole or
part of a row so I can format it ?

thanks for your help ... Roger

 

[RyanH]

Try this. This will Bold and turn the text Red for the row and columns you
are referring to.

With Range(Cells(lastfilledrow + 2, 5), Cells(lastfilledrow + 2, 9))
.Font.ColorIndex = 3
.Font.Bold = True
End With

 

[Joel]

with Worksheets("Report(metArb)")
lastfilledrow = .Range("B3").Row
.Cells(lastfilledrow + 2, 5).Value = "Total"
sumcellformula = "=sum(h2:h" & lastfilledrow & ")"
.Cells(lastfilledrow + 2, 8).Formula = sumcellformula
sumcellformula = "=sum(i2:i" & lastfilledrow & ")"
.Cells(lastfilledrow + 2, 9).Formula = sumcellformula
sumcellformula = "=sum(j2:j" & lastfilledrow & ")"
Set ColorRange = .Range("E" & LastRow & ":I" & LastRow)
end with

 

[RyanH]

I took the liberty of cleaning up your code a bit. I would give this a try.
It should do the same thing. If not, let me know.

Option Explicit

Sub TEST()

Dim lngLastRow As Long

With Sheets("Report(metArb)")
lngLastRow = .Cells(Rows.Count, "B").End(xlDown).Row + 2
.Cells(lngLastRow, 5).Value = "Total"
.Cells(lngLastRow, 8).Formula = "=sum(H2:H" & lngLastRow & ")"
.Cells(lngLastRow, 9).Formula = "=sum(I2:I" & lngLastRow & ")"
End With

End Sub

Hope this helps! If so, let me know and click "YES" below.

 

[Rick Rothstein]

Cleaning up your cleaning up<g>....

Sub TEST()

Dim lngLastRow As Long

With Sheets("Sheet2")
lngLastRow = .Cells(Rows.Count, "B").End(xlUp).Row + 2
.Cells(lngLastRow, 5).Value = "Total"
.Cells(lngLastRow, 8).Formula = "=sum(H2:H" & (lngLastRow - 2) & ")"
.Cells(lngLastRow, 9).Formula = "=sum(I2:I" & (lngLastRow - 2) & ")"
End With

End Sub

 

[Ro477]

Ryan, thanks, very helpful. But there is no YES to click at the foot of your
reply, sorry ... but just the same thanks for your reply ... Roger