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

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
--
Cheers,
Ryan


"Ro477" wrote:

> 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
>
>
>

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

"Ro477" wrote:

> 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
>
>
>

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.
--
Cheers,
Ryan


"Ro477" wrote:

> 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
>
>
>

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

--
Rick (MVP - Excel)


"RyanH" <(E-Mail Removed)> wrote in message
news:F871B6E2-B0E0-41BD-86F3-(E-Mail Removed)...
>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.
> --
> Cheers,
> Ryan
>
>
> "Ro477" wrote:
>
>> 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
>>
>>
>>

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


"RyanH" <(E-Mail Removed)> wrote in message
news:F871B6E2-B0E0-41BD-86F3-(E-Mail Removed)...
>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.
> --
> Cheers,
> Ryan
>
>
> "Ro477" wrote:
>
>> 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
>>
>>
>>