I took out the 2nd if/then block to make it easier to read. I have a
question on how to do the pagebreak at the end of the departments and
a question on how to create the very last row header. TIA


This macro creates a gray row header for the first department row of
each group of departments but I need it to page break after the last
department in each group.
Since this macro starts at the bottom and goes up to create the rows
do I need another separate macro to do the page breaks since I want to
go(down) instead of up to the last one in each department. This macro
goes (up) to the first department and creates a gray row.

The other problem is that it does not put a gray row header on the
very first row. Presumably because it has no next line to compare
itself with on the first row. Should I just add a row header for the
first line since that will always be the first row for that
department? Is that the way to do
it?


Public Sub ColorHeaders()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim sDeptName As String
Dim sStatusName As String
Dim sPrevDeptID
Dim sDeptID
Dim rng As Range
With ActiveWorkbook.Worksheets("Sheet1")
FirstRow = 2
LastRow = .Cells(.Rows.Count, 16).End(xlUp).Row
For iRow = LastRow To FirstRow + 1 Step -1

sDeptID = .Cells(iRow, 16)
sNextDeptID = .Cells(iRow + 1, 16)
'first if block creates the Item Name headers

If sDeptID <> sNextDeptID Then .Rows(iRow).PageBreak = xlPageBreakManual

If .Cells(iRow, 16).Value = .Cells(iRow - 1, 16).Value Then
'do nothing if the department is the same as previous


Else
'if the department is a new department add the row header
sDeptName = .Cells(iRow, 17).Value
.Rows(iRow).Insert
.Range(.Cells(iRow, 1), .Cells(iRow, 26)).Interior.ColorIndex = 15

.Cells(iRow, 4).Value = sDeptName
.Cells(iRow, 4).Font.Bold = True
.Cells(iRow, 4).Font.Size = 14
.Cells(iRow, 4).RowHeight = 18

End If
Next iRow
End With

End Sub