Trigger Conditional Formats Before Printing

My staff and spreadsheet users will tell you that any time I build a spreadsheet, there are always shaded cells on the grid. I preach that "Green means go", and make sure that any cell they enter data in has a green background. I also use blue backgrounds for "update these sometimes" cells, like tax rates. If cells are left with no colouring, though: everyone here knows that they should be left alone.

Often, I have a few data entry cells or blocks on multiple worksheets. While this follows good spreadsheet design principles, it does have a bit of a side effect: When you print the worksheets, the green backgrounds print as well. While they're great for telling the user where to input data manually, it's distracting when you are looking at a printed (presentation version) of your data or report. So the question you may be asking is what can we do about it?

We could create a full new report that essentially duplicates what we've got, but then it's both a maintenance headache and performance hit. Instead, we can use a conditional format to hide the colour on the cells when we go to print the sheet.

This is actually really easy, if it weren't for the fact that you probably want to do it automatically. That involves dipping into some VBA. If you're not comfortable working with VBA though, read on anyway. You can still do the setup for this and control it manually.

This article illustrates three things:

Step 1: Setting up the Control Point

The first thing to do is create a cell to control what mode the workbook is in. i.e. Printing Mode or Working mode. There's lots of ways to do this. In our files we always set up a worksheet dedicated to being a "Control Panel", and it's there that I would set a cell on that worksheet to hold a "Yes" or "No" value:

As you can see, this approach can be used to drive a few other things as well, such as dates for column headers, the type of data pulled from a PivotTable and more.)

If you're wondering why not True/False values, we used Yes/No as it's more readable to others if they have to work with the spreadsheet.

Step 2: Name the Range

The next thing to do is assign a name to the PrintMode value. In this case, We used the name rngPrintMode to the cell B11 on the control panel worksheet. While this isn't truly required to make this work, it is much easier to work with the named range later, rather than try to remember the cell reference. It also becomes key when you move to the VBA side, as VBA doesn't update its reference to the cell when a line is inserted above. Named ranges, on the other hand, do update automatically.. For this reason alone, it is highly recommended that you use named ranges when referring to worksheet ranges in VBA.

Step 3: Set up the Conditional Format

The steps shown below are for Excel 2007, but with a little ingenuity, you can make this work in 2003. We're assuming that you already have a green (or other) background on your data entry cell at this point.

• Select the data entry cell(s) that you want to hide the colour on
• On the Home Tab--> Conditional Formatting--> New Rule then create a formula as shown below:

• Click Format
• From the "Fill" tab, click "No Color"
• Click OK twice

At this point, the conditional format has been set up. As an optional step, you can set the "Stop If True" option in 2007, as this allows us to use multiple conditional formats on the same section, yet ignore them all for printing. You won't find this in Excel 2003 though. (Also, if you want to stop all formats, make sure the rule is at the top of the list!)

As far as the setting up the conditional formatting, you're actually done. Change the value on the rngPrintMode cell to "Yes", and your conditional formats should kick in, removing any applied background colours. Now you can print without the data entry cells conspicuously marked! Change it back to "No", and the colours re-appear.

Just a hint here: if you want to leave this as a manual control, set up a data validation list to control the field. Debra Dalgleish has a great article on doing that which you can find right here.

Step 4: Adding Automation

To make this automatically kick in when you hit print, and set itself back once it's done, you'll need to add a couple of VBA procedures to your project.

In a STANDARD MODULE, add the following:

Public Sub PrintingActive(Optional bStatus = False)
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Toggle the printing mode variable to control
' conditional format set
Dim rngInfo As Range

    '!!! Set Your Worksheet Here !!!
    Set rngInfo = Worksheets("Control Panel").Range("rngPrintMode")

    'Check the status and set the cell accordingly
    If bStatus = True Then
        rngInfo.Value = "Yes"
    Else
        rngInfo.Value = "No"
    End If

End Sub

You'll need to change your worksheet name for the one that holds the named range you defined earlier. Just change "Control Panel" to "Your Worksheet Name".

Next, go to the THISWORKBOOK module, and put the following in it:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Toggle the conditional formats for printing
    Call PrintingActive(True)
    Application.OnTime Now + TimeValue("00:00:01"), "PrintingActive"
End Sub

At this point, providing you had things working manually in step 3, things should just work by hitting the print button. The BeforePrint routine will call the PrintingActive routine, which will flip the rngPrintMode variable to TRUE. This will in turn trigger the conditional format rule. The Application.OnTime then schedules a call of the PrintingActive routine to run in one second. The worksheet(s) are then printed.

The unfortunate part about this approach is that we have to rely on using OnTime, which kicks off a routine based on time, not necessarily when the existing routine completes. Theoretically, if you had a really huge print job, the call to PrintingActive could be triggered before the job is complete. While unlikely, you can always just add a few seconds to the TimeValue to work around this issue if you experience it. (I.e. "00:00:15" will turn it into a 15 second delay.)

The ideal solution would have been to leverage a Workbook_AfterPrint event, but sadly one does not exist.

Conclusion

That's it! Just click the print button and the green cells change their backgrounds to white (transparent), the file will print, and then the colour(s) are reinstated. And it all happens without even having the change the cell value manually.

This article was originally published on my blog on May 11, 2009. The original can be found here.