PDA

View Full Version : Test



hobbiton73
05-21-2014, 11:16 PM
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