Interior color of cells up to the last cell with data in a column

Question

Here is my problem:

• I have an excel spreadsheet with sheet2 the overall data and sheet3 to sheet8 with data based on years copied from sheet2.
• In each sheet(Sheet3-8)values in each row (excluding row 1) are added and to sum of each row in column D.
• To have an interior colour and bold font in column D from row 2 up to the last row with data I used the following codes (example in sheet3).
• The spreadsheet is to be updated using a command button in sheet2.
• When I run the code separately in the VB developer sometimes it works sometimes it causes run time error 1004.
• When I try to update the spreadsheet using the button it always causes the error.

{Sheet3.Range("D2", Cells(Rows.Count, "D").End(xlUp).Offset(-2, 0)).Interior.Color = RGB(255, 192, 0)}
{Sheet3.Range("D2", Cells(Rows.Count, "D").End(xlUp).Offset(-2, 0)).Font.Bold=True}

The full code is as shown below:

{Sub YearlyForcast2011_2012()

Sheet3.Columns("D").HorizontalAlignment = xlRight
Dim j As Integer
Dim lastrow2 As Long
Dim sumrange As Long


lastrow2 = Sheet3.Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To lastrow2

Sheet3.Cells(j, 4).Value = Sheet3.Cells(j, 5).Value + Sheet3.Cells(j, 6).Value + Sheet3.Cells(j, 7).Value + Sheet3.Cells(j, 8) + Sheet3.Cells(j, 9).Value + Sheet3.Cells(j, 10).Value + Sheet3.Cells(j, 11).Value +      Sheet3.Cells(j, 12).Value + Sheet3.Cells(j, 13).Value + Sheet3.Cells(j, 14).Value + Sheet3.Cells(j, 15).Value + Sheet3.Cells(j, 16).Value

Next j

   sumrange = Sheet3.Cells(Rows.Count, "D").End(xlUp).Row
   Sheet3.Range("D" & sumrange + 2).Formula = "=SUM(D2:D" & sumrange & ")"
   Sheet3.Range("D" & sumrange + 2).Font.Bold = True
   Sheet3.Range("D" & sumrange + 2).Font.Size = 12
   Sheet3.Range("D" & sumrange + 2).Font.Color = RGB(255, 0, 0)
   Sheet3.Range("D2", Cells(Rows.Count, "D").End(xlUp).Offset(-2, 0)).Interior.Color = RGB(255, 192, 0)
   Sheet3.Range("D2", Cells(Rows.Count, "D").End(xlUp).Offset(-2, 0)).Font.Bold=True

Sheet3.Range("c" & sumrange + 2).Value = "TOTAL 2011-2011 YEARLY FORCAST"
Sheet3.Range("c" & sumrange + 2).Font.Bold = True
Sheet3.Range("c" & sumrange + 2).Font.Size = 12
Sheet3.Range("c" & sumrange + 2).Font.Color = RGB(255, 0, 0)
Sheet3.Range("c" & sumrange + 2).HorizontalAlignment = xlRight


Application.ScreenUpdating = False
Application.CutCopyMode = False


 End Sub
}

Can someone help me to avoid the error and update the spreadsheet keeping the interior color and bold font in each D column of the sheets?

Answer 1

Try this:
Before lines

Sheet3.Range("D2", Cells(Rows.Count, "D").End(xlUp).Offset(-2, 0)).Interior.Color = RGB(255, 192, 0)
Sheet3.Range("D2", Cells(Rows.Count, "D").End(xlUp).Offset(-2, 0)).Font.Bold=True  

add

lastrow2 = Sheet3.Cells(Rows.Count, "D").End(xlUp).Row

and change your lines to this:

Sheet3.Range("D2", Cells(lastrow2, "D")).Interior.Color = RGB(255, 192, 0)
Sheet3.Range("D2", Cells(lastrow2, "D")).Font.Bold = True

You could also change this:

For j = 2 To lastrow2

Sheet3.Cells(j, 4).Value = Sheet3.Cells(j, 5).Value + Sheet3.Cells(j, 6).Value + Sheet3.Cells(j, 7).Value + Sheet3.Cells(j, 8) + Sheet3.Cells(j, 9).Value + Sheet3.Cells(j, 10).Value + Sheet3.Cells(j, 11).Value +      Sheet3.Cells(j, 12).Value + Sheet3.Cells(j, 13).Value + Sheet3.Cells(j, 14).Value + Sheet3.Cells(j, 15).Value + Sheet3.Cells(j, 16).Value

Next j  

To this:

Sheet3.Range("D2:D" & lastrow2).Formula = "=SUM(E2:T2)"  

To run through sheets 3-8 the whole code will look like that (remember the j is the index of the sheet! Adjust if necessary):

Sub YearlyForcast2011_2012()

Dim j As Integer
Dim lastrow2 As Long
Dim sumrange As Long

For j = 3 To 8

    Sheets(j).Columns("D").HorizontalAlignment = xlRight

    lastrow2 = Sheets(j).Cells(Rows.Count, 1).End(xlUp).Row

     Sheets(j).Range("D2:D" & lastrow2).Formula = "=SUM(E2:T2)"

    sumrange = Sheets(j).Cells(Rows.Count, "D").End(xlUp).Row

     Sheets(j).Range("D" & sumrange + 2).Formula = "=SUM(D2:D" & sumrange & ")"
     Sheets(j).Range("D" & sumrange + 2).Font.Bold = True
     Sheets(j).Range("D" & sumrange + 2).Font.Size = 12
     Sheets(j).Range("D" & sumrange + 2).Font.Color = RGB(255, 0, 0)

    lastrow2 = Sheets(j).Cells(Rows.Count, "D").End(xlUp).Row

     Sheets(j).Range("D2", Cells(lastrow2, "D")).Interior.Color = RGB(255, 192, 0)
     Sheets(j).Range("D2", Cells(lastrow2, "D")).Font.Bold = True

     Sheets(j).Range("c" & sumrange + 2).Value = "TOTAL 2011-2011 YEARLY FORCAST"
     Sheets(j).Range("c" & sumrange + 2).Font.Bold = True
     Sheets(j).Range("c" & sumrange + 2).Font.Size = 12
     Sheets(j).Range("c" & sumrange + 2).Font.Color = RGB(255, 0, 0)
     Sheets(j).Range("c" & sumrange + 2).HorizontalAlignment = xlRight

Next j

Application.ScreenUpdating = False
Application.CutCopyMode = False

End Sub