PDA

View Full Version : Print Without Background Fill Color



zoom38
12-04-2007, 02:56 PM
Hello all,
Is there a way to print out the worksheet without the background cell fill colors and patterns? My worksheet has colors in the cells and looks nice on the screen, however when I print it out it doesn't look so nice because of the background fill.
I've done a search on the net and in vba help but was unsuccessful. Here is the print routine Im using.


Sub PrintSetup()

Dim LastRow As Long
Application.ScreenUpdating = False
Sheets("AddedPaymentCalculator").Activate

LastRow = Cells(9, 7).Value + 18

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.PaperSize = xlPaperLetter
.PrintArea = "$A$1:$M" & LastRow
.Zoom = 100
' .Zoom = False
'.FitToPagesWide = 1
End With
ActiveSheet.PrintOut
Application.ScreenUpdating = True
End Sub


Thanks
Gary

zoom38
12-05-2007, 05:59 PM
This is what I came up with:


Sub PrintSetup()
Dim LastRow As Long
Dim PrintRange As Range
Application.ScreenUpdating = False
Sheets("AddedPaymentCalculator").Activate

LastRow = Cells(9, 7).Value + 18

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.PaperSize = xlPaperLetter
.PrintArea = "$A$1:$M" & LastRow
.Zoom = 100
' .Zoom = False
'.FitToPagesWide = 1
End With

'Change The Cell Fill Color To White For Printing In Black & White
With ActiveSheet.Range("$A$1:$M" & LastRow)
.Interior.ColorIndex = 0 '0 = white, 15 = light gray, 19 = yellow
End With

'Print The Worksheet
ActiveSheet.PrintOut

'Change The Cell Fill Color To Original Color
With ActiveSheet.Range("$A$1:$M" & LastRow)
.Interior.ColorIndex = 15
End With
With ActiveSheet.Range("$C$4:$D$6")
.Interior.ColorIndex = 28 'Light Blue
End With
With ActiveSheet.Range("$F$4:$H$6")
.Interior.ColorIndex = 28
End With
With ActiveSheet.Range("$D$8:$D$11")
.Interior.ColorIndex = 6
End With
With ActiveSheet.Range("$D$13")
.Interior.ColorIndex = 28
End With
Application.ScreenUpdating = True
End Sub


However this does not affect cell fill by conditional formatting.
Gary

lucas
12-05-2007, 09:16 PM
I would suggest that you copy the sheet..remove all formula's, links, etc. format entire sheet to black and white, etc. print it and delete it instead of trying to keep up with all the changes and then change it back.

Charlize
12-06-2007, 02:01 AM
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.PaperSize = xlPaperLetter
.PrintArea = "$A$1:$M" & LastRow
.Zoom = 100
'Added line for printing black and white
'put it back to false after the printing
.BlackAndWhite = True
' .Zoom = False
'.FitToPagesWide = 1
End With