Clearing shading before printing
 

Is there a way to clear all cell shading or even a
selection of cells when the user prints a spreadsheet? I
would like to have this connected to the user printing
the document. Also, is there a way to place the shading
back after the document prints? Thanks. Matt

Clearing shading before printing
 

You can use this event for this

This example will change the backround color of Range("A1:A10") from red to
nothing and print the sheet.
After printing it will place the red shading back

This example will only work for Sheet1
You can press the printbutton on the menubar


Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
.Range("A1:A10").Interior.ColorIndex = xlNone
.PrintOut
.Range("A1:A10").Interior.ColorIndex = 3 'red
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Matt" wrote in message ...
Is there a way to clear all cell shading or even a
selection of cells when the user prints a spreadsheet? I
would like to have this connected to the user printing
the document. Also, is there a way to place the shading
back after the document prints? Thanks. Matt

Clearing shading before printing
 

You must copy this event in the Thisworkbook module

Right click on the Excel icon next to File in the menubar
And choose View code

You are now in the Thisworkbook module
Paste the Event in this place
Alt-Q to go back to Excel


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ron de Bruin" wrote in message ...
You can use this event for this

This example will change the backround color of Range("A1:A10") from red to
nothing and print the sheet.
After printing it will place the red shading back

This example will only work for Sheet1
You can press the printbutton on the menubar


Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
.Range("A1:A10").Interior.ColorIndex = xlNone
.PrintOut
.Range("A1:A10").Interior.ColorIndex = 3 'red
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Matt" wrote in message ...
Is there a way to clear all cell shading or even a
selection of cells when the user prints a spreadsheet? I
would like to have this connected to the user printing
the document. Also, is there a way to place the shading
back after the document prints? Thanks. Matt

Clearing shading before printing
 

You can include in the PageSetup line the code

With ActiveSheet.PageSetup
.BlackAndWhite = True
End with

That will cause the printing to disregard any cell shadings, but the
shadings remain on the screen after printing.

"Matt" wrote in message ...
Is there a way to clear all cell shading or even a
selection of cells when the user prints a spreadsheet? I
would like to have this connected to the user printing
the document. Also, is there a way to place the shading
back after the document prints? Thanks. Matt