Make cells invisible to printer?

[HoundofCullin]

G'day.
I have created a sort of ticket printout form. Where the ticket is
filled out then printed onto these specially made sheets of paper with
all sorts of fields that are normally filled out by hand (but my ticket
makes fills it out for us). The only problem is that I now have a mess
of empty cells that have no label of anysort. What I need is to either
color the surrounding cells another color (so that all but the cells
that need to be filled out are say... red) and then when the form is
printed have it ignore the colored cells OR Have some sort of label the
printer ignores but can be plainly seen and then the person can fill
out the appropriate cells.


It is seemingly easy... but I can't for the life of me figure out how
to go about it.

 

[Kevin Sprinkel]

I can't seem to find out how to do this either, however,
there's always a way. :^)

Assuming that you cannot exclude your labels from the
contiguous cell range, and that you are unable to move
this application to Access whose report writer would make
this a snap, how about inserting another worksheet into
the workbook that draws all the data from the input page?
You could then create a macro and assign it to a custom
button to switch sheets, print, and return:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/22/2003 by Kevin G. Sprinkel
'

'
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
Sheets("Sheet1").Select
End Sub

HTH
Kevin Sprinkel
Becker & Frondorf

 

[Paul B]

Hound, how about recording a macro to set the font color to white print the
sheet and then set it back?

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

 

[Ron de Bruin]

Try this

1) It will make the font white
2) Print
3) Make the font black again

Sub test1()
'Range with one area
With ActiveSheet
.Range("B10:B14").Font.ColorIndex = 2
.PrintOut
.Range("B10:B14").Font.ColorIndex = 1
End With
End Sub

Sub test2()
'Range with more areas
With ActiveSheet
.Range("A1:A3,B10:B14,C12").Font.ColorIndex = 2
.PrintOut
.Range("A1:A3,B10:B14,C12").Font.ColorIndex = 1
End With
End Sub

 

[Debra Dalgleish]

As a non-macro solution, you could use conditional formatting to show
and hide the non-printing cells.

1. Select the cells that you don't want printed
2. Choose Format>Conditional Formatting
3. From the first dropdown, choose Formula Is
4. In the box, type a formula that refers to an empty cell,
outside the print area: =$M$2="x"
5. Click the Format button, and choose white as the Font colour
6. Click OK, click OK
7. Now, type an x in cell M2
8. Cells with conditional formatting will change to white font.
Remove the x from M2, and the font returns to the original colour.