No Highlighted Areas when Printing

[Graham]

Is it possible to highlight areas in a worksheet to guide the user as to
where entries are to be made but which do not show up when the sheet is
printed. eg borders, shading, patterns, colours are all good to show these
locations but all show up on the printed sheet. I appreciate code could be
written to remove these prior to printing and put them back after but this
seems a bit of a sledgehammer to crack a nut. Would value any advice.

Kind regards
Graham Haughs
Turriff, Scotland

 

[Bill Kuunders]

You could use text boxes or other shapes.
colour them
and in <format> select <properties> and deselect <print object>
Regards
Bill K

 

[RagDyer]

<<"colours are all good to show these locations but all show up on the
printed sheet">>

*Not* if you do:
<File> <PageSetUp> <Sheet> tab,
and under Print, check "Black and White".
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Is it possible to highlight areas in a worksheet to guide the user as to
where entries are to be made but which do not show up when the sheet is
printed. eg borders, shading, patterns, colours are all good to show these
locations but all show up on the printed sheet. I appreciate code could be
written to remove these prior to printing and put them back after but this
seems a bit of a sledgehammer to crack a nut. Would value any advice.

Kind regards
Graham Haughs
Turriff, Scotland

 

[Debra Dalgleish]

You can colour the cells, and use conditional formatting to change the
cells to No Fill when you're ready to print. There's an example here:

http://www.contextures.com/xlCondFormat03.html#Print

 

[Dave Peterson]

One more way with a major catch.

Show the drawing toolbar. (View|toolbars|drawing)

Insert a nice shape (callout under the autoshapes).

position it nicely and format it even better.

Right click on it and choose Format Autoshape
Properties tab|Uncheck Print Object
Colors and lines Tab
Make it 100 transparent.

The bad thing about this is that it's really easy to type into the
autoshape--not the cell underneath it.

If you protect the worksheet (with the underlying cells unlocked), it'll make it
more difficult to screw it up.

=====
but I'd use code.

I create a range name for the highlighted cells.

Select your range (click on the first cell and ctrl-click on subsequent cells).

Type
HiLight
in the namebox (to the left of the formula bar)
and hit enter

Then show the forms toolbar and drop a button on the sheet.
(make it so it doesn't print!)

Then assign it a macro like:

Option Explicit
Sub printMeNicely()

Dim myRng As Range
Dim myColorIndex As Long

Set myRng = ActiveSheet.Range("hilight")

myColorIndex = myRng(1).Interior.ColorIndex
myRng.Interior.ColorIndex = xlNone
ActiveSheet.PrintOut preview:=True
myRng.Interior.ColorIndex = myColorIndex

End Sub

It uses the same color for each cell. (and remove preview:=true when you're
ready to kill a few trees.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Graham]

Thanks to everyone for the replies and the range of options which will
certainly solve the problem.

Graham