On print - remove fill

**dgkindy** · 02-07-2009, 06:25 PM

I am looking for a method to remove the shading on a sheet that helps the user to fill in the required date but when I print, I want the shading to be removed or not printed when I hit the print button. I would like to just use the default print but on excel rather then created a new button.

**mudraker** · 02-07-2009, 06:46 PM

You can use a before print macro

The problem will be to restore the shading after prinying

'These instructions pre typed & are worded to cater for the novice programmer

'To install macro to correct location

'Copy this macro
'GoTo Excel
'Open VB Editor by pressing Alt + F11
'Just below the menus & toolbars on the left you should see Project - VbaProject window
'In this window you will see ThisWorkbook
'double click on ThisWorkbook - opens ThisWorkbook module sheet
'Paste macro code into big window on right

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   Cells.Interior.ColorIndex = xlNone
End Sub

**broro183** · 02-07-2009, 07:46 PM

If using Mudraker's approach I suggest "naming the cell range" in Excel & then referring to the named range in the macro. This prevents the removal of shading in any other cells that you do want to see on the printed page (eg if you have any coloured "traffic lights" set up).

Another approach that I have read about which doesn't quite comply with your request but may be worth trying - instead of removing the shading, just have the initial shading in your file changed to a very light colour that hardly shows on the printed page.

hth
Rob

**shg** · 02-07-2009, 10:03 PM

Maybe File > Page Setup, Sheet tab, tick Black and White

**martindwilson** · 02-07-2009, 10:23 PM

how about just copying the sheet paste special formats to a spare sheet ,clear them on printed sheet then past back special after
something like
(which im sure can be tidied up)

cleanb4print()
Application.ScreenUpdating = False
    Sheets("Sheet1").Select 'CHANGE TO YOUR WORKSHEET
    Cells.Select
    Selection.Copy
    Sheets("Sheet3").Select 'CHANGE TO ANY SPARE WORKSHEET
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Sheets("Sheet1").Select 'CHANGE TO YOUR WORKSHEET
    Selection.Interior.ColorIndex = xlNone
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Sheets("Sheet3").Select 'CHANGE TO ANY SPARE WORKSHEET
    Selection.Copy
    Sheets("Sheet1").Select 'CHANGE TO YOUR WORKSHEET
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Sheets("Sheet3").Select 'CHANGE TO ANY SPARE WORKSHEET
    Selection.Interior.ColorIndex = xlNone
    Range("A1").Select
    Sheets("Sheet1").Select 'CHANGE TO YOUR WORKSHEET
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub