Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Conditional Formatting After Page Break
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Conditional Formatting After Page Break
from a previous post:
DataFreakFromUtah View profile More options May 27 2004, 11:50 am Newsgroups: microsoft.public.excel.programming From: (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 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Conditional Formatting After Page Break
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: (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 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Conditional Formatting After Page Break
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 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: (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 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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Conditional Formatting After Page Break
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 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: (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 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to disable page break | Excel Discussion (Misc queries) | |||
Conditional Formatting - Before Page Break | Excel Discussion (Misc queries) | |||
disable page break preview button | Excel Programming | |||
Disable page break preview button | Excel Programming | |||
Disable Page Break Preview Button | Excel Programming |