Clearing shading before printing

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

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
 
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
 
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
 
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.
 
Back
Top