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" <(E-Mail Removed)> wrote in message
news:04690179-6812-48EF-8906-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:04690179-6812-48EF-8906-(E-Mail Removed)...
> 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
<(E-Mail Removed)> 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