Temporarily Disable Conditional Formatting when Printing

[Maffia]

I'm after a macro to temporarily disable conditional formatting for a range of cells when printing.

Or even just temporarily set the cell background colour to "no fill" and then have it restored after printing.

The reason for this is I'm creating a bending schedule for steel reinforcement for concrete, and would like to warn the user that an invalid dimension has been entered using conditional formatting, however if the user really wants to continue I don't want the conditional formatting to show on prints, is this possible?

Many thanks.

[romperstomper]

I would use a helper cell for this. Add a check to the conditional format so that it checks if the helper cell is 1 as well as the original checks. Then you can use a BeforePrint event to set the helper cell to 0, thereby turning off the CF, print, then reset the cell to 1.

[Maffia]

Thank you for the quick response, can you expand on the BeforePrint event, I'm new to using macros.

[romperstomper]

In the ThisWorkbook module:

Please Login or Register  to view this content.

then in a normal module:

Please Login or Register  to view this content.

adjusting the range to suit.

[royUK]

Unless you are using coloour elsewhere in the print, why not

Please Login or Register  to view this content.

[Maffia]

Thank you, I will try that.

I have colours elsewhere and text that I need hidden when printing, so is set to print in white which comes out black if set to print in black and white.

OK, got it all working.

Also can I get this to apply to any copies of the first sheet made, or just globally?

And is it possible to get it working and allowing changes to the sheet name?

[Maffia]

Can anyone help with the last two points?

[romperstomper]

Assuming you are using the altered CF, then just have all the CF point to the same flag cell and alter that. It will then affect all copies of the sheets. In order to allow changing the sheet name, use its code name in the routines rather than its tab name - e.g.:

Please Login or Register  to view this content.

[teylyn]

Only, in versions pre 2010 you can not refer to a cell in a different sheet in a CF formula.

Therefore, create a range name that holds the flag instead of a cell in a specific sheet.

Toggle the value of the named range with code like

Please Login or Register  to view this content.

and in the conditional formatting formula refer to the range CFflag instead of a specific cell in a specific sheet.

[romperstomper]

Or just name the cell and use that name in the CF formulas.

[Maffia]

Thank you very much, have got it working how I wanted.