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?
Sheet3.Range("D2", Sheet3.Cells(Rows.Count, "D").End(xlUp).Offset(-2, 0)).Interior.Color = RGB(255, 192, 0)
(same with the next line). See this. – user4039065