Test

[hobbiton73]

Sub PortAddWorkFormat()
    Dim LastRow As Long
    Dim ws As Worksheet
     
    Application.ScreenUpdating = False
     
    For Each ws In Worksheets(Array("All C&R Forecast", "B&C Portfolio Forecast", "BT Portfolio Forecast", "Corp Portfolio Forecast", "E&C Portfolio Forecast", "PT Portfolio Forecast"))
        ws.Select
         
        With Range("B2")
            .Value = "Month of Extract"
        End With
        With Range("B2, B5, B36")
            .HorizontalAlignment = xlCenter
            .Interior.ColorIndex = 11
            With .Font
                .Name = "Lucida Sans"
                .Bold = True
                .Size = 11
                .ColorIndex = 2
            End With
        End With
        With Range("B3")
            .Value = Format(Date, "1 mmm yy")
            .NumberFormat = "mmm yy"
            .HorizontalAlignment = xlCenter
            .Interior.ColorIndex = 37
            With .Font
                .Name = "Lucida Sans"
                .Bold = True
                .Size = 10
            End With
        End With
        With Range("B8").Resize(6)
            .Value = Application.Transpose(Array("Flexible Staff FTE exc. BAS Consultants:", "All Direct Activities Forecast FTE exc. BAS Consultants:", "All Enhancements Forecast FTE exc. BAS Consultants:", "All Indirect Activities Forecast FTE exc. BAS Consultants:", "All Overheads Activities Forecast FTE exc. BAS Consultants:", "All Projects Forecast FTE exc. BAS Consultants:"))
            With .Font
                .Name = "Lucida Sans"
                .Bold = True
                .Size = 10
            End With
        End With
        LastRow = Cells(Rows.Count, "B").End(xlUp).Row
        If LastRow >= StartRow Then
            With Range("C8:N13" & ",C39:N" & LastRow)
                .NumberFormat = "#,##0.00"
                .HorizontalAlignment = xlCenter
            End With
            With Range("B39:B" & LastRow)
                With .Font
                    .Name = "Lucida Sans"
                    .Bold = True
                    .Size = 10
                End With
            End With
        End If
         
        If InStr(1, "B&C Portfolio Forecast, BT Portfolio Forecast, Corp Portfolio Forecast, E&C Portfolio Forecast, PT Portfolio Forecast", ws.Name) > 0 Then
             
            LastRow = Cells(Rows.Count, "B").End(xlUp).Row
            If LastRow >= StartRow Then
                With Range("C39:N" & LastRow)
                    .NumberFormat = "#,##0.00"
                    .HorizontalAlignment = xlCenter
                End With
                With Range("B39:B" & LastRow)
                    With .Font
                        .Name = "Lucida Sans"
                        .Bold = True
                        .Size = 10
                    End With
                End With
            End If
        End If
            ws.Columns("B:N").AutoFit
        Next ws
    End Sub