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("A7:P30").
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

page setup:

ActiveSheet.PageSetup.BlackAndWhite= True



"terry w" wrote in message
...
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("A7:P30").
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

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.


"terry w" wrote in message
...
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("A7:P30").
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

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("A7:P30")

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
--
jb


"terry w" wrote:

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("A7:P30").
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

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

"john" wrote:

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("A7:P30")

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
--
jb


"terry w" wrote:

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("A7:P30").
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

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
--
Data Hog


"terry w" wrote:

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

Print in Draft Quality?


Gord Dibben MS Excel MVP

On Fri, 4 Dec 2009 18:53:01 -0800, terry w
wrote:

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("A7:P30").
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