Print macro to exclude a cell

[JaimeLex]

Hi,

I'm completely new to macros and having a tough time getting this to work.
I've got an IF statement in a merged cell of my worksheet (Cell B10). Text
appears in the cell if a certain condition is met.

I want to create a macro for this workbook only that will change the font in
cell B10 to white prior to printing and back to the red I selected after
printing. Is there a way for this to run automatically when a user prints
this workbook?

Any help would be great as this is my first macro and I'm not sure where to
start...

 

[Dave Peterson]

I'd start by looking at Ron de Bruin's site:
http://www.rondebruin.nl/print.htm#Hide

 

[JaimeLex]

Thank you so much! That was perfect. It worked to a "T".

 

[JLatham]

Your code would need to go into the Workbook's _BeforePrint() event handler.

This is going to be a bit tough to do because you've got cell C10 being
setup for display with conditional formatting. IF the color of the cell's
font is part of the conditional formatting then just setting the cell's
regular/default font color is not going to have any effect - the conditional
formatting will override it.

A way around the above, if that's the case, would be to set up another
condition ahead of the one you have already that says if a certain value is
in some other cell, then to set the font color to white. Then your
_BeforePrint() code would set the value in that other cell to the value that
causes the font to become white.

The problem is made more complicated by the fact that while the
_BeforePrint() code can set that special value in the other cell, you cannot
include code in it to change that value so that the text in C10 is once again
colored red. All the code in that module is executed before the printing is
started.

What you can do is then add code to the sheet that C10 is in, in that
worksheet's _SelectionChange() event code to set that other special value to
anything other than the "make C10 font white" value. That would fire each
time you move from one area/cell on that sheet to another area/cell on it.
Since you can't move around while printing, any movement you make would be
while not printing and would cause C10 to appear visible to you ... you just
have to click another cell on that sheet after printing is completed to make
C10 visible again.

 

[Dave Peterson]

Ron does nice work!

Thank you so much! That was perfect. It worked to a "T".

 

[JLatham]

And he thinks of a nice way to attack things - I was thinking of attacking it
through the _BeforePrint() process, as opposed to "make the change ... do the
print ... reverse the change" which is not a 100% satisfactory solution -
could be if you could detect when the printing has finished/file has been
sent to the spooler.