VBA Format to Sheet Array

[hobbiton73]

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

[mancubus]

hi, hobbiton. just check the next line to see what is missing. :)

[hobbiton73]

Hi @mancubus, thank you for taking the time to reply to my post.

Ok, so this is obviously the
ws.Range("B6:R6") but could you possibly tell me please, is there a method of writing this without having to put the
ws.Range("B6:R6") at the beginning of each line?

Many thanks and kind regards

[Kenneth Hobs]

Use With. e.g.

Sub FormatCells()
Dim ws As Worksheet

For Each ws In Worksheets(Array("Direct Activities", "Enhancements", "Indirect Activities", "Overheads", "Projects"))
With 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
End With
Next ws
End Sub

[mancubus]

looking at the entirety of the code... "With...End With" block is also missing. and some other parts as well..

[mancubus]

following code is an my assumption for what you are trying to achieve. :)
Sub FormatCells() Dim ws As Worksheet For Each ws In Worksheets(Array("Direct Activities", "Enhancements", "Indirect Activities", "Overheads", "Projects")) With ws With .Range("B6:R6") .Font.Bold = True .Font.ColorIndex = 2 .Font.Name = "Lucida Sans" .Font.Size = 10 .NumberFormat = "mmm-yy" .HorizontalAlignment = xlCenter .Interior.ColorIndex = 11 End With With .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))" End With With .Range("B2:C2") .Font.Bold = True .Font.Name = "Lucida Sans" .Font.Size = 12 .HorizontalAlignment = xlCenter .Interior.ColorIndex = 11 End With With .Range("B3:C3") .Font.Bold = True .Cells.Font.Name = "Lucida Sans" .Cells.Font.Size = 11 .Interior.ColorIndex = 37 .HorizontalAlignment = xlCenter End With .Columns("B:Q").EntireColumn.AutoFit End With Next wsEnd Sub

[hobbiton73]

Hi @Kenneth Hobs, thank you for taking the time to reply to my post and for the guidance.

Kind regards

[mancubus]

........

[hobbiton73]

Hi @mancubus, thank you very much for coming back to me with this and for putting the solution together.

Unfortunately I think something may have gone awry with the formatting?

Kind Regards

[mancubus]

you are welcome.if you can post your workbook with fake data and displaying before and after situations, we may solve it.

[hobbiton73]

Hi @mancubus, thank you but I don't think that I need to trouble you any longer. The guidance which I've received from yourself and @Kenneth Hobs has set me along the right path.

Kind Regards