I used conditional formatting so that a particular itme does not duplicate
itself in a report. However, at the top of each page I need to disable the
conditional formatting. I would certainly appreciate it if someone could
show me how to detect where the automatic page break occurs and either disble
or remove the conditional formatting for that row or cell.

from a previous post:

DataFreakFromUtah View profile
More options May 27 2004, 11:50 am

Newsgroups: microsoft.public.excel.programming
From: aztecbrainsurg...@yahoo.com (DataFreakFromUtah)
Date: 27 May 2004 09:50:19 -0700
Local: Thurs, May 27 2004 11:50 am
Subject: MANUAL & AUTOMATIC Horizontal Pagebreak Report - an example
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
No question here, just a procedure for the archive.

THIS PROCEDURE INCLUDES/RETURNS THE PRINT PAGE NUMBER
(FOR EACH MANUAL & AUTOMATIC PAGEBREAK) IN THE REPORT


Sub PageBreaksHorizontalReportALL()
'Creates a new report worksheet that contains the row of numbers
'of all MANUAL & AUTOMATIC horizontal pagebreaks on the
'active worksheet. INCLUDES PRINT PAGE NUMBER IN REPORT


Dim cell As Range
Dim PageBreakSheet As Worksheet
Dim TargetWorksheet As Worksheet
Dim hb As HPageBreak
Dim VPC As Integer, HPC As Integer
Dim VPB As VPageBreak
Dim NumPage As Integer


Dim Row As Integer


On Error Resume Next


'Add a new worksheet
Application.ScreenUpdating = False
Set TargetWorksheet = ActiveWorkbook.ActiveSheet
Set PageBreakSheet = ActiveWorkbook.Worksheets.Add
PageBreakSheet.Name = "Pagebreaks in " & TargetWorksheet.Name


If ActiveSheet.PageSetup.Order = xlDownThenOver Then
HPC = ActiveSheet.HPageBreaks.Count + 1
VPC = 1
Else
VPC = ActiveSheet.VPageBreaks.Count + 1
HPC = 1
End If
NumPage = 1


'Set up the column headings for Report worksheet
With PageBreakSheet
Range("A1") = "PageBreak Row"


'Optional 2nd reference that can be used to return
'a cells value that has the horizontal page break
Range("B1") = "PageBreak Cell Value"
Range("C1") = "Print Page Number"
Range("A1:C1").Font.Bold = True
End With


'Process each pagebreak
Row = 2
For Each hb In TargetWorksheet.HPageBreaks
'Derive Page Number of page break
NumPage = NumPage + VPC


With PageBreakSheet
Cells(Row, 1).Value = hb.Location.Row


'Optional 2nd reference that can be used to return
'a cell's value that has the horizontal page break on
'it's row. 'In this case it takes the value in column A
with
'the row number of the page break. Adjust
Cells(hb.Location.Row, #) as
'needed.


Cells(Row, 2).Value =
CStr(TargetWorksheet.Cells(hb.Location.Row, 1).Value)


Cells(Row, 3).Value = NumPage


Row = Row + 1


End With


Next


'Adjust column widths on Report sheet
PageBreakSheet.Columns("A:C").AutoFit
Application.StatusBar = False


'Select a cell on the top of the report worksheet
Range("A2").Select


End Sub


you can always search the newsgroup, too.
hope this helps
susan


On Feb 28, 10:37 am, JR_06062005
<JR06062...@discussions.microsoft.com> wrote:
> I used conditional formatting so that a particular itme does not duplicate
> itself in a report. However, at the top of each page I need to disable the
> conditional formatting. I would certainly appreciate it if someone could
> show me how to detect where the automatic page break occurs and either disble
> or remove the conditional formatting for that row or cell.

It does appear that buried in this procedure is the answer to my question.
Thanks

"Susan" wrote:

> from a previous post:
>
> DataFreakFromUtah View profile
> More options May 27 2004, 11:50 am
>
> Newsgroups: microsoft.public.excel.programming
> From: aztecbrainsurg...@yahoo.com (DataFreakFromUtah)
> Date: 27 May 2004 09:50:19 -0700
> Local: Thurs, May 27 2004 11:50 am
> Subject: MANUAL & AUTOMATIC Horizontal Pagebreak Report - an example
> Reply to author | Forward | Print | Individual message | Show original
> | Report this message | Find messages by this author
> No question here, just a procedure for the archive.
>
> THIS PROCEDURE INCLUDES/RETURNS THE PRINT PAGE NUMBER
> (FOR EACH MANUAL & AUTOMATIC PAGEBREAK) IN THE REPORT
>
>
> Sub PageBreaksHorizontalReportALL()
> 'Creates a new report worksheet that contains the row of numbers
> 'of all MANUAL & AUTOMATIC horizontal pagebreaks on the
> 'active worksheet. INCLUDES PRINT PAGE NUMBER IN REPORT
>
>
> Dim cell As Range
> Dim PageBreakSheet As Worksheet
> Dim TargetWorksheet As Worksheet
> Dim hb As HPageBreak
> Dim VPC As Integer, HPC As Integer
> Dim VPB As VPageBreak
> Dim NumPage As Integer
>
>
> Dim Row As Integer
>
>
> On Error Resume Next
>
>
> 'Add a new worksheet
> Application.ScreenUpdating = False
> Set TargetWorksheet = ActiveWorkbook.ActiveSheet
> Set PageBreakSheet = ActiveWorkbook.Worksheets.Add
> PageBreakSheet.Name = "Pagebreaks in " & TargetWorksheet.Name
>
>
> If ActiveSheet.PageSetup.Order = xlDownThenOver Then
> HPC = ActiveSheet.HPageBreaks.Count + 1
> VPC = 1
> Else
> VPC = ActiveSheet.VPageBreaks.Count + 1
> HPC = 1
> End If
> NumPage = 1
>
>
> 'Set up the column headings for Report worksheet
> With PageBreakSheet
> Range("A1") = "PageBreak Row"
>
>
> 'Optional 2nd reference that can be used to return
> 'a cells value that has the horizontal page break
> Range("B1") = "PageBreak Cell Value"
> Range("C1") = "Print Page Number"
> Range("A1:C1").Font.Bold = True
> End With
>
>
> 'Process each pagebreak
> Row = 2
> For Each hb In TargetWorksheet.HPageBreaks
> 'Derive Page Number of page break
> NumPage = NumPage + VPC
>
>
> With PageBreakSheet
> Cells(Row, 1).Value = hb.Location.Row
>
>
> 'Optional 2nd reference that can be used to return
> 'a cell's value that has the horizontal page break on
> 'it's row. 'In this case it takes the value in column A
> with
> 'the row number of the page break. Adjust
> Cells(hb.Location.Row, #) as
> 'needed.
>
>
> Cells(Row, 2).Value =
> CStr(TargetWorksheet.Cells(hb.Location.Row, 1).Value)
>
>
> Cells(Row, 3).Value = NumPage
>
>
> Row = Row + 1
>
>
> End With
>
>
> Next
>
>
> 'Adjust column widths on Report sheet
> PageBreakSheet.Columns("A:C").AutoFit
> Application.StatusBar = False
>
>
> 'Select a cell on the top of the report worksheet
> Range("A2").Select
>
>
> End Sub
>
>
> you can always search the newsgroup, too.
> hope this helps
> susan
>
>
> On Feb 28, 10:37 am, JR_06062005
> <JR06062...@discussions.microsoft.com> wrote:
> > I used conditional formatting so that a particular itme does not duplicate
> > itself in a report. However, at the top of each page I need to disable the
> > conditional formatting. I would certainly appreciate it if someone could
> > show me how to detect where the automatic page break occurs and either disble
> > or remove the conditional formatting for that row or cell.
>
>
>

i'm sorry.
i'm just batting 1000 today!
maybe i should shut up for the rest of the day.

susan

On Feb 28, 11:25 am, JR_06062005
<JR06062...@discussions.microsoft.com> wrote:
> It does appear that buried in this procedure is the answer to my question.
> Thanks
>
>
>
> "Susan" wrote:
> > from a previous post:
>
> > DataFreakFromUtah View profile
> > More options May 27 2004, 11:50 am
>
> > Newsgroups: microsoft.public.excel.programming
> > From: aztecbrainsurg...@yahoo.com (DataFreakFromUtah)
> > Date: 27 May 2004 09:50:19 -0700
> > Local: Thurs, May 27 2004 11:50 am
> > Subject: MANUAL & AUTOMATIC Horizontal Pagebreak Report - an example
> > Reply to author | Forward | Print | Individual message | Show original
> > | Report this message | Find messages by this author
> > No question here, just a procedure for the archive.
>
> > THIS PROCEDURE INCLUDES/RETURNS THE PRINT PAGE NUMBER
> > (FOR EACH MANUAL & AUTOMATIC PAGEBREAK) IN THE REPORT
>
> > Sub PageBreaksHorizontalReportALL()
> > 'Creates a new report worksheet that contains the row of numbers
> > 'of all MANUAL & AUTOMATIC horizontal pagebreaks on the
> > 'active worksheet. INCLUDES PRINT PAGE NUMBER IN REPORT
>
> > Dim cell As Range
> > Dim PageBreakSheet As Worksheet
> > Dim TargetWorksheet As Worksheet
> > Dim hb As HPageBreak
> > Dim VPC As Integer, HPC As Integer
> > Dim VPB As VPageBreak
> > Dim NumPage As Integer
>
> > Dim Row As Integer
>
> > On Error Resume Next
>
> > 'Add a new worksheet
> > Application.ScreenUpdating = False
> > Set TargetWorksheet = ActiveWorkbook.ActiveSheet
> > Set PageBreakSheet = ActiveWorkbook.Worksheets.Add
> > PageBreakSheet.Name = "Pagebreaks in " & TargetWorksheet.Name
>
> > If ActiveSheet.PageSetup.Order = xlDownThenOver Then
> > HPC = ActiveSheet.HPageBreaks.Count + 1
> > VPC = 1
> > Else
> > VPC = ActiveSheet.VPageBreaks.Count + 1
> > HPC = 1
> > End If
> > NumPage = 1
>
> > 'Set up the column headings for Report worksheet
> > With PageBreakSheet
> > Range("A1") = "PageBreak Row"
>
> > 'Optional 2nd reference that can be used to return
> > 'a cells value that has the horizontal page break
> > Range("B1") = "PageBreak Cell Value"
> > Range("C1") = "Print Page Number"
> > Range("A1:C1").Font.Bold = True
> > End With
>
> > 'Process each pagebreak
> > Row = 2
> > For Each hb In TargetWorksheet.HPageBreaks
> > 'Derive Page Number of page break
> > NumPage = NumPage + VPC
>
> > With PageBreakSheet
> > Cells(Row, 1).Value = hb.Location.Row
>
> > 'Optional 2nd reference that can be used to return
> > 'a cell's value that has the horizontal page break on
> > 'it's row. 'In this case it takes the value in column A
> > with
> > 'the row number of the page break. Adjust
> > Cells(hb.Location.Row, #) as
> > 'needed.
>
> > Cells(Row, 2).Value =
> > CStr(TargetWorksheet.Cells(hb.Location.Row, 1).Value)
>
> > Cells(Row, 3).Value = NumPage
>
> > Row = Row + 1
>
> > End With
>
> > Next
>
> > 'Adjust column widths on Report sheet
> > PageBreakSheet.Columns("A:C").AutoFit
> > Application.StatusBar = False
>
> > 'Select a cell on the top of the report worksheet
> > Range("A2").Select
>
> > End Sub
>
> > you can always search the newsgroup, too.
> > hope this helps
> > susan
>
> > On Feb 28, 10:37 am, JR_06062005
> > <JR06062...@discussions.microsoft.com> wrote:
> > > I used conditional formatting so that a particular itme does not duplicate
> > > itself in a report. However, at the top of each page I need to disable the
> > > conditional formatting. I would certainly appreciate it if someone could
> > > show me how to detect where the automatic page break occurs and either disble
> > > or remove the conditional formatting for that row or cell.- Hide quoted text -
>
> - Show quoted text -

Sorry, didn't mean to sound unappreciative. Your answer did help tremendously.

"Susan" wrote:

> i'm sorry.
> i'm just batting 1000 today!
> maybe i should shut up for the rest of the day.
>
> susan
>
> On Feb 28, 11:25 am, JR_06062005
> <JR06062...@discussions.microsoft.com> wrote:
> > It does appear that buried in this procedure is the answer to my question.
> > Thanks
> >
> >
> >
> > "Susan" wrote:
> > > from a previous post:
> >
> > > DataFreakFromUtah View profile
> > > More options May 27 2004, 11:50 am
> >
> > > Newsgroups: microsoft.public.excel.programming
> > > From: aztecbrainsurg...@yahoo.com (DataFreakFromUtah)
> > > Date: 27 May 2004 09:50:19 -0700
> > > Local: Thurs, May 27 2004 11:50 am
> > > Subject: MANUAL & AUTOMATIC Horizontal Pagebreak Report - an example
> > > Reply to author | Forward | Print | Individual message | Show original
> > > | Report this message | Find messages by this author
> > > No question here, just a procedure for the archive.
> >
> > > THIS PROCEDURE INCLUDES/RETURNS THE PRINT PAGE NUMBER
> > > (FOR EACH MANUAL & AUTOMATIC PAGEBREAK) IN THE REPORT
> >
> > > Sub PageBreaksHorizontalReportALL()
> > > 'Creates a new report worksheet that contains the row of numbers
> > > 'of all MANUAL & AUTOMATIC horizontal pagebreaks on the
> > > 'active worksheet. INCLUDES PRINT PAGE NUMBER IN REPORT
> >
> > > Dim cell As Range
> > > Dim PageBreakSheet As Worksheet
> > > Dim TargetWorksheet As Worksheet
> > > Dim hb As HPageBreak
> > > Dim VPC As Integer, HPC As Integer
> > > Dim VPB As VPageBreak
> > > Dim NumPage As Integer
> >
> > > Dim Row As Integer
> >
> > > On Error Resume Next
> >
> > > 'Add a new worksheet
> > > Application.ScreenUpdating = False
> > > Set TargetWorksheet = ActiveWorkbook.ActiveSheet
> > > Set PageBreakSheet = ActiveWorkbook.Worksheets.Add
> > > PageBreakSheet.Name = "Pagebreaks in " & TargetWorksheet.Name
> >
> > > If ActiveSheet.PageSetup.Order = xlDownThenOver Then
> > > HPC = ActiveSheet.HPageBreaks.Count + 1
> > > VPC = 1
> > > Else
> > > VPC = ActiveSheet.VPageBreaks.Count + 1
> > > HPC = 1
> > > End If
> > > NumPage = 1
> >
> > > 'Set up the column headings for Report worksheet
> > > With PageBreakSheet
> > > Range("A1") = "PageBreak Row"
> >
> > > 'Optional 2nd reference that can be used to return
> > > 'a cells value that has the horizontal page break
> > > Range("B1") = "PageBreak Cell Value"
> > > Range("C1") = "Print Page Number"
> > > Range("A1:C1").Font.Bold = True
> > > End With
> >
> > > 'Process each pagebreak
> > > Row = 2
> > > For Each hb In TargetWorksheet.HPageBreaks
> > > 'Derive Page Number of page break
> > > NumPage = NumPage + VPC
> >
> > > With PageBreakSheet
> > > Cells(Row, 1).Value = hb.Location.Row
> >
> > > 'Optional 2nd reference that can be used to return
> > > 'a cell's value that has the horizontal page break on
> > > 'it's row. 'In this case it takes the value in column A
> > > with
> > > 'the row number of the page break. Adjust
> > > Cells(hb.Location.Row, #) as
> > > 'needed.
> >
> > > Cells(Row, 2).Value =
> > > CStr(TargetWorksheet.Cells(hb.Location.Row, 1).Value)
> >
> > > Cells(Row, 3).Value = NumPage
> >
> > > Row = Row + 1
> >
> > > End With
> >
> > > Next
> >
> > > 'Adjust column widths on Report sheet
> > > PageBreakSheet.Columns("A:C").AutoFit
> > > Application.StatusBar = False
> >
> > > 'Select a cell on the top of the report worksheet
> > > Range("A2").Select
> >
> > > End Sub
> >
> > > you can always search the newsgroup, too.
> > > hope this helps
> > > susan
> >
> > > On Feb 28, 10:37 am, JR_06062005
> > > <JR06062...@discussions.microsoft.com> wrote:
> > > > I used conditional formatting so that a particular itme does not duplicate
> > > > itself in a report. However, at the top of each page I need to disable the
> > > > conditional formatting. I would certainly appreciate it if someone could
> > > > show me how to detect where the automatic page break occurs and either disble
> > > > or remove the conditional formatting for that row or cell.- Hide quoted text -
> >
> > - Show quoted text -
>
>
>