hobbiton73
09-18-2013, 06:05 AM
Hi, I wonder whether someone can help me please.
I'm trying to run the following script to apply formatting to multiple sheets within an array.
Sub FormatCells()
Dim ws As Worksheet
For Each ws In Worksheets(Array("Direct Activities", "Enhancements", "Indirect Activities", "Overheads", "Projects"))
ws.Range("B6:R6").Font.Bold = True
.Font.ColorIndex = 2
.Cells.Font.Name = "Lucida Sans"
.Cells.Font.Size = 10
.NumberFormat = "mmm-yy"
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 11
ws.Range("B2").Value = "Current Month"
.Offset(, 1).Value = "Working Days"
.Offset(1, 0).Value = MonthName(Month(Date))
.Offset(1, 1).FormulaR1C1 = "=NETWORKDAYS(R[1]C-DAY(R[1]C)+1,DATE(YEAR(R[1]C),MONTH(R[1]C)+1,0))"
ws.Range("B2:C2").Font.Bold = True
.Cells.Font.Name = "Lucida Sans"
.Cells.Font.Size = 12
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 11
ws.Range("B3:C3").Font.Bold = True
.Cells.Font.Name = "Lucida Sans"
.Cells.Font.Size = 11
.Interior.ColorIndex = 37
.HorizontalAlignment = xlCenter
ws.Columns("B:Q").EntireColumn.AutoFit
Next ws
End Sub
The problem I have is that when I try to run this, I receive a compile error, highlighting this line as the cause:
.Font.ColorIndex = 2
Could someone perhaps explain to me please why, and what would be the correct way in which to write this type of script.
Many thanks and kind regards
I'm trying to run the following script to apply formatting to multiple sheets within an array.
Sub FormatCells()
Dim ws As Worksheet
For Each ws In Worksheets(Array("Direct Activities", "Enhancements", "Indirect Activities", "Overheads", "Projects"))
ws.Range("B6:R6").Font.Bold = True
.Font.ColorIndex = 2
.Cells.Font.Name = "Lucida Sans"
.Cells.Font.Size = 10
.NumberFormat = "mmm-yy"
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 11
ws.Range("B2").Value = "Current Month"
.Offset(, 1).Value = "Working Days"
.Offset(1, 0).Value = MonthName(Month(Date))
.Offset(1, 1).FormulaR1C1 = "=NETWORKDAYS(R[1]C-DAY(R[1]C)+1,DATE(YEAR(R[1]C),MONTH(R[1]C)+1,0))"
ws.Range("B2:C2").Font.Bold = True
.Cells.Font.Name = "Lucida Sans"
.Cells.Font.Size = 12
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 11
ws.Range("B3:C3").Font.Bold = True
.Cells.Font.Name = "Lucida Sans"
.Cells.Font.Size = 11
.Interior.ColorIndex = 37
.HorizontalAlignment = xlCenter
ws.Columns("B:Q").EntireColumn.AutoFit
Next ws
End Sub
The problem I have is that when I try to run this, I receive a compile error, highlighting this line as the cause:
.Font.ColorIndex = 2
Could someone perhaps explain to me please why, and what would be the correct way in which to write this type of script.
Many thanks and kind regards