Clearing background color in printed copies

[terry w]

My Sheet has various regions of with various light background colors. This
is to help staff enter data in the right places! When I print the Sheet,
though, I don't want any background colors to show for the Range("A730").
Is there a good way to do this? (I still want the background colors to show
on the screen, just not in the printed copy.)

Terry

 

[JLGWhiz]

page setup:

ActiveSheet.PageSetup.BlackAndWhite= True

 

[JLGWhiz]

Disregard that. The colors would print as shades of gray.

What you wouldhave to do is either change all the interior colors to white
or remove them. If the print area is not too large, i.e. one page, you
could just copy the values to another sheet and print that sheet.

 

[john]

see if this approach helps
you can adjust page setup as required.

Sub PrintBlackAndWhite()
Dim Sh As Worksheet
Dim Rng As Range
Dim ShNew As Worksheet
Dim ws1 As Worksheet

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

'name of worksheet where your data is stored
'change as required
Set ws1 = Worksheets("Sheet1")

'add temp worksheet
Set ShNew = Worksheets.Add(after:=Worksheets(Worksheets.Count))


Set Rng = ws1.Range("A730")

Rng.Copy

With ShNew

.Pictures.Paste Link:=True
.Shapes(1).PictureFormat.ColorType = msoPictureBlackAndWhite


'adjust page
With .PageSetup

.PrintArea = "$A$1:$P$24"

.LeftMargin = Application.InchesToPoints(0.1)
.RightMargin = Application.InchesToPoints(0.1)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = True
.Zoom = 100
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = 0

End With

.PrintOut

.Delete

End With

ws1.Activate

With Application
.ScreenUpdating = True
.DisplayAlerts = True
.CutCopyMode = False
End With

End Sub

 

[terry w]

John - thanks. (that was a lot of typing!)
Terry W.

 

[J_Knowles]

Terry W.,

FYI - Highlight the VBA code in the message, press ctrl+c (copies
highlighted code), go to your VBA module and press ctrl+v (paste). That
takes about 5 seconds, beats the heck out of typing all the code in by hand.

HTH

 

[Gord Dibben]

Print in Draft Quality?


Gord Dibben MS Excel MVP