if/then block within another if/then block needs a loop

[Janis]

I got an if/then algorithm from this list that colors the first row of each
department with a light gray. Nice. It starts at the bottom and compares
each previous line, dept ID field with the next line field and if it doesn't
match then it is a new department and it colors the line gray.

I managed to insert within the first if part of the if/then statement
another if/then block that takes the status ID field and creates colored
headers for each type of status within that department using the exact same
algorithm. When it gets to the next department then it starts over creating
the next department row also with colored rows for each status. YEAH!
Problem: Now the last requirement is to create a page break after each
department. A page of the report goes to each department.

The problem with this is I am going bottom to top and now I need to go from
top to bottom to create the page break at the end of each department.

You don't have to look at the macro which I included, I just need the
algorithm.

how do I do that? THANKS!

Start at the bottom,
if the last row is the same as the next row
do not add a department header
however check the status field if it
is not the same as the previous one
then add a colored row for the status type.
else
if it is the same check the next row
else
if the last row is not the same as the next row
then it is a new department add a header row.

go to the next row up

Do you see what I mean? I can't put a page break in the first if/then because
then it will break the page and it can't go up the column to check for the
next department?
TIA
------

Public Sub ColorDivHeaders()

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

'first if block creates the Item Name headers
If .Cells(iRow, 16).Value = .Cells(iRow - 1, 16).Value Then
'do nothing if the department is the same as previous
' create the status row headers

If .Cells(iRow, 19).Value = .Cells(iRow - 1, 19).Value Then
' do nothing
Else
sStatusName = .Cells(iRow, 18).Value
.Rows(iRow).Insert
.Range(.Cells(iRow, 1), .Cells(iRow, 26)).Interior.ColorIndex = 48
.Range(.Cells(iRow, 1), .Cells(iRow, 26)).Value = sStatusName
.Cells(iRow, 3).Font.Bold = True
.Cells(iRow, 3).Font.Size = 12
.Cells(iRow, 3).RowHeight = 16
.Cells(iRow, 3).Font.Color = vbWhite
.HorizontalAlignment = xlCenterAcrossSelection
End If


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, 3).Value = sDeptName
.Cells(iRow, 3).Font.Bold = True
.Cells(iRow, 3).Font.Size = 14
.Cells(iRow, 3).RowHeight = 18

End If
Next iRow
End With
End Sub

 

[Sandy]

add

..Rows(iRow).PageBreak = xlPageBreakManual

at the end of your else statement, I think this is what you wanted to
do

Sandy