this macro doesn't compile on the HPageBreak. It thinks it is a variable not
a method?
What I'm trying to do is force a page break above every department header
row. Every lite gray row is a department header. I want a page break at the
end of every department range so this should work.


Sub SSPPageBreak2()
Dim Rng As Range
Dim rngToSearch As Range
With ActiveSheet
Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
End With
For Each Rng In rngToSearch
If Rng.Interior.ColorIndex = 15 Then HPageBreaks.Add befo=.Range
Next Rng
End Sub

ActiveSheet.HPageBreaks.Add Befo=rng

--
Regards,
Tom Ogilvy




"Janis" wrote:

this macro doesn't compile on the HPageBreak. It thinks it is a variable not
a method?
What I'm trying to do is force a page break above every department header
row. Every lite gray row is a department header. I want a page break at the
end of every department range so this should work.


Sub SSPPageBreak2()
Dim Rng As Range
Dim rngToSearch As Range
With ActiveSheet
Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
End With
For Each Rng In rngToSearch
If Rng.Interior.ColorIndex = 15 Then HPageBreaks.Add befo=.Range
Next Rng
End Sub