Sub Insert_Row()
Dim lRow As Long, nxtRow As Long, ColCnt As Long, a As Long
Dim MySheets As Variant
MySheets = Array("FY09 Installation Support", "FY09 Install", "FY09 Purchase", "FY09 CF Discretionary Grants", "FY09 CF LOI", "FY08 Purchase", "FY08 Installation Support", "FY08 CF Discretionary Grants", "FY07 Sup Install Support", "FY07 CF Install Non-LOI", "FY07 Sup Purchase", "FY05 CF Carryover Install", "FY04 Recovery Funds", "FY05 Recovery Funds", "FY08 Safety Carryover", "FY09 Safety", "FY09 Transport Canada")
Application.ScreenUpdating = False
For a = LBound(MySheets) To UBound(MySheets)
With Worksheets(MySheets(a))
ColCnt = .Columns.Count
lRow = .Range("B" & .Rows.Count).End(xlUp).Row
For nxtRow = lRow To 1 Step -1
If .Range("B" & nxtRow) = "PR Block" Then
.Range("B" & nxtRow).EntireRow.Insert
With .Range("B" & nxtRow).EntireRow.Interior
.ColorIndex = 16
.Pattern = xlSolid
End With
.Range(.Cells(nxtRow, 1), .Cells(nxtRow, ColCnt)).BorderAround ColorIndex:=0, Weight:=xlMedium
End If
Next nxtRow
End With
Next a
Application.ScreenUpdating = True
End Sub
Bookmarks