THanks very much, this macro creates all the right page breaks except the
first one.
The first row dept doesn't need a pagebreak above it. So I tried to add an
ANd if to the conditional. And if it is the first row then don't do the
page break.
I don't know how to add a second condition to the first condition. Row 1 is
the header line, row 2 is the first department header. i don't want a page
break there.

Sub SSPPageBreak2()
Dim Rng As Range
Dim rngToSearch As Range
With ActiveSheet
Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))

For Each Rng In rngToSearch
If Rng.Interior.ColorIndex = 15 AND if rng < .Range(r2) Then

ActiveSheet.HPageBreaks.Add befo=Rng
End If
Next Rng
End With
End Sub

If you want to exclude row 1, why not start in row 2

Sub SSPPageBreak2()
Dim Rng As Range
Dim rngToSearch As Range
With ActiveSheet
Set rngToSearch = .Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))

For Each Rng In rngToSearch
If Rng.Interior.ColorIndex = 15 Then

ActiveSheet.HPageBreaks.Add befo=Rng
End If
Next Rng
End With
End Sub

or to go with your current approach:

Sub SSPPageBreak2()
Dim Rng As Range
Dim rngToSearch As Range
With ActiveSheet
Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))

For Each Rng In rngToSearch
If Rng.Interior.ColorIndex = 15 AND if rng.row 1 Then

ActiveSheet.HPageBreaks.Add befo=Rng
End If
Next Rng
End With
End Sub

--
Regards,
Tom Ogilvy




"Janis" wrote:

THanks very much, this macro creates all the right page breaks except the
first one.
The first row dept doesn't need a pagebreak above it. So I tried to add an
ANd if to the conditional. And if it is the first row then don't do the
page break.
I don't know how to add a second condition to the first condition. Row 1 is
the header line, row 2 is the first department header. i don't want a page
break there.

Sub SSPPageBreak2()
Dim Rng As Range
Dim rngToSearch As Range
With ActiveSheet
Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))

For Each Rng In rngToSearch
If Rng.Interior.ColorIndex = 15 AND if rng < .Range(r2) Then

ActiveSheet.HPageBreaks.Add befo=Rng
End If
Next Rng
End With
End Sub

Thanks I'll try it your way, to answer your question becuase I thought going
from the bottom was easiest.

"Tom Ogilvy" wrote:

If you want to exclude row 1, why not start in row 2

Sub SSPPageBreak2()
Dim Rng As Range
Dim rngToSearch As Range
With ActiveSheet
Set rngToSearch = .Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))

For Each Rng In rngToSearch
If Rng.Interior.ColorIndex = 15 Then

ActiveSheet.HPageBreaks.Add befo=Rng
End If
Next Rng
End With
End Sub

or to go with your current approach:

Sub SSPPageBreak2()
Dim Rng As Range
Dim rngToSearch As Range
With ActiveSheet
Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))

For Each Rng In rngToSearch
If Rng.Interior.ColorIndex = 15 AND if rng.row 1 Then

ActiveSheet.HPageBreaks.Add befo=Rng
End If
Next Rng
End With
End Sub

--
Regards,
Tom Ogilvy




"Janis" wrote:

THanks very much, this macro creates all the right page breaks except the
first one.
The first row dept doesn't need a pagebreak above it. So I tried to add an
ANd if to the conditional. And if it is the first row then don't do the
page break.
I don't know how to add a second condition to the first condition. Row 1 is
the header line, row 2 is the first department header. i don't want a page
break there.

Sub SSPPageBreak2()
Dim Rng As Range
Dim rngToSearch As Range
With ActiveSheet
Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))

For Each Rng In rngToSearch
If Rng.Interior.ColorIndex = 15 AND if rng < .Range(r2) Then

ActiveSheet.HPageBreaks.Add befo=Rng
End If
Next Rng
End With
End Sub

Starting in row 2 and using xlup are not inconsistent as you should see from
the code I altered. (unless the whole column is empty)

--
Regards,
Tom Ogilvy


"Janis" wrote:

Thanks I'll try it your way, to answer your question becuase I thought going
from the bottom was easiest.

"Tom Ogilvy" wrote:

If you want to exclude row 1, why not start in row 2

Sub SSPPageBreak2()
Dim Rng As Range
Dim rngToSearch As Range
With ActiveSheet
Set rngToSearch = .Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))

For Each Rng In rngToSearch
If Rng.Interior.ColorIndex = 15 Then

ActiveSheet.HPageBreaks.Add befo=Rng
End If
Next Rng
End With
End Sub

or to go with your current approach:

Sub SSPPageBreak2()
Dim Rng As Range
Dim rngToSearch As Range
With ActiveSheet
Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))

For Each Rng In rngToSearch
If Rng.Interior.ColorIndex = 15 AND if rng.row 1 Then

ActiveSheet.HPageBreaks.Add befo=Rng
End If
Next Rng
End With
End Sub

--
Regards,
Tom Ogilvy




"Janis" wrote:

THanks very much, this macro creates all the right page breaks except the
first one.
The first row dept doesn't need a pagebreak above it. So I tried to add an
ANd if to the conditional. And if it is the first row then don't do the
page break.
I don't know how to add a second condition to the first condition. Row 1 is
the header line, row 2 is the first department header. i don't want a page
break there.

Sub SSPPageBreak2()
Dim Rng As Range
Dim rngToSearch As Range
With ActiveSheet
Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))

For Each Rng In rngToSearch
If Rng.Interior.ColorIndex = 15 AND if rng < .Range(r2) Then

ActiveSheet.HPageBreaks.Add befo=Rng
End If
Next Rng
End With
End Sub