Differente certain cells without visibility on print?

  • Thread starter Thread starter Dan Williams
  • Start date Start date
D

Dan Williams

I'd love to discover a way to differentiate the unlocked cells from
the locked cells, so the users will know which ones they can change,
but I don't want to make the cells another color because I don't want
anything to be gray when the sheet is printed.

The closest idea I've had is this: Change the Window color in Windows
to some color other than white, and then explicitly fill the unlocked
cells with white. That would work well, but it would require all the
users to change their Window color, and I'm not willing to ask them to
do that. (It would change the background color in Word and everything
else, too.)

The cells will sometimes start out blank, so a different text color
will not work.

Any ideas?
 
Dan,

I don't fully understand you problem because you first
state that you do not want to use colors and then you
state that a close idea you have is to change the colors.

I always use colors to distinguish unlocked cells. When
you say that you do not want gray to print out, try going
to Page Setup\Sheet and clicking the Black & White box.
When certain cells are non-white colors, they will print
out with white backgrounds if this option is checked.

Steve
 
How about giving the range of cells that are unprotected a nice name. Then
before you print, you change the color, then print, then set it back after the
print.

Put something like this in the Thisworkbook module:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Worksheets("sheet1").Range("myInput").Interior.ColorIndex = xlNone
Application.OnTime Now + TimeSerial(0, 0, 5), "ResetColors"
End Sub

Then in a general module, put something like this:

Option Explicit
Sub resetcolors()
Worksheets("sheet1").Range("myInput").Interior.ColorIndex = 3
End Sub

If you use different colors for different ranges, just add a few more lines.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Thanks! Those are both good ideas. I'm thinking I actually do want
some of the locked cells to have a color, so looks like I'll have to
do the complicated one.

Thanks again.
Dan

P.S. Steve, I wasn't talking about adding any colors in Excel (just
white). I was only adding the color to Windows itself, via Control
Panel/Appearance. If I changed that "Window" color, everything would
still print white, but the Excel and Word backgrounds, etc., would all
appear the new color all the time.
 
Back
Top