Printing a workbook without colour

**oggie1205** · 2013-11-12, 01:28 PM

Hi
I am an electrician, and have made an electrical certificate that covers several sheets. I have made some cells coloured by conditional formatting, so that it is easy to see if cells are empty and needs some kind of input. It also has a coloured logo on top of each sheet.
What I need to do, is to be able to print all the sheets without colour in the cells but keep the logo coloured, as sometimes these coloured cells will not have an input. But dont need to be coloured on final certificate.

Hope I explained what I require ok?

If somebody has a solution that a complete novice can cope with, I know some simple formulas but no indepth stuff, so a step by step solution would be great.

Thank you.

**NoS** · 2013-11-12, 04:20 PM

SAVE YOUR WORKBOOK BEFORE DOING THIS TO PRESERVE YOUR CONDITIONAL FORMATTING.

Alt - F11 to open the Visual Basic Environment
From the menu click Insert
From the drop down click Module
Paste this code into the Module that opens

Code:

Option Explicit

Sub RemoveCF()
    Dim sht As Integer
    Dim response As Integer
    
    response = MsgBox("Be sure to save your workbook before continuing" & (Chr$(13)) & _
                      "Click  YES  to continue or NO to Exit this routine", 276, _
                      "HAVE YOU SAVED YOUR WORKBOOK")
           
    If response = vbNo Then Exit Sub
    
    For sht = 1 To Sheets.Count
        Sheets(sht).Cells.FormatConditions.Delete
    Next sht
    
End Sub

close the Visual Basic Environment
Hit Alt - F8
Select the RemoveCF macro and run it

The worksheets can now be printed with all conditional formatting removed from the workbook.
Make sure you DO NOT SAVE the workbook at this point because all the conditional formatting you did have is gone.

Hope this is of some assistance.

**oggie1205** · 2013-11-12, 05:01 PM

NoS

Thank you for the reply, i followed your instructions, but there seems to be a problem. When i run the macro it stops at Sheets(sht).Cells.FormatConditions.Delete
this is high lighted when I press the debug button.

Any advice?

Originally Posted by NoS

SAVE YOUR WORKBOOK BEFORE DOING THIS TO PRESERVE YOUR CONDITIONAL FORMATTING.

Alt - F11 to open the Visual Basic Environment
From the menu click Insert
From the drop down click Module
Paste this code into the Module that opens

Code:

Option Explicit

Sub RemoveCF()
    Dim sht As Integer
    Dim response As Integer
    
    response = MsgBox("Be sure to save your workbook before continuing" & (Chr$(13)) & _
                      "Click  YES  to continue or NO to Exit this routine", 276, _
                      "HAVE YOU SAVED YOUR WORKBOOK")
           
    If response = vbNo Then Exit Sub
    
    For sht = 1 To Sheets.Count
        Sheets(sht).Cells.FormatConditions.Delete
    Next sht
    
End Sub

close the Visual Basic Environment
Hit Alt - F8
Select the RemoveCF macro and run it

The worksheets can now be printed with all conditional formatting removed from the workbook.
Make sure you DO NOT SAVE the workbook at this point because all the conditional formatting you did have is gone.

Hope this is of some assistance.

**Ken Puls** · 2013-11-12, 05:28 PM

Can I offer an alternate approach?

If you have a read of this article, you'll find an approach that won't remove all your colours from the workbook, it just hides them before you print. Basically you'd just need to update your conditional formats to also test the state of rngPrintMode as well.

So if I had a format that triggered red if the cell was empty like this:
=LEN($A$5)=0

Then I'd update it to:
=AND(LEN($A$5)=0,rngPrintMode="No")

That would force the format to only trigger when print mode is off.

**NoS** · 2013-11-12, 05:41 PM

Thanks Ken, much appreciated.

Learn something new every day.

**Ken Puls** · 2013-11-12, 05:56 PM

Happy to! That's what I love about the forum world. There's almost always an alternate way to accomplish a goal.

**NoS** · 2013-11-12, 09:54 PM

oggie, are you making out OK with this? If not get back to us and we can help out.

While I would now suggest Ken's approach to this, I am curious as to why the other macro failed when you tried it.

What error number and message was displayed when you clicked debug?
Did any of your worksheets have the conditional formatting removed?

**oggie1205** · 2013-11-13, 09:06 AM

Hi,

The error that came up was
Run Time error `1004`
application-defined or object-defind error

Thanks again for your help with this.

Originally Posted by NoS

oggie, are you making out OK with this? If not get back to us and we can help out.

While I would now suggest Ken's approach to this, I am curious as to why the other macro failed when you tried it.

What error number and message was displayed when you clicked debug?
Did any of your worksheets have the conditional formatting removed?

**Ken Puls** · 2013-11-13, 07:20 PM

Oggie, is the data sensitive? Could you upload a sample workbook? (The board has that functionality if you go into the Advanced reply mode.)

**oggie1205** · 2013-11-14, 09:53 AM

Ken,

Attached is a workbook (certificate), All the coloured cell are a guide for the electrical inspector, entering details as needed, each sheet is usually password protected so no details can be changed apart for those that are coloured. If the inpector does leave some cell blank (coloured) I need to be able to print the workbook without those cells printing in colour. But I do require the logo at the top printing in colour. At the moment we are taking off the protection to remove the conditional formatting of those cell that the inspector has left, which when you are doing several, takes time.

Thanks for your help

Oggie

Originally Posted by Ken Puls

Oggie, is the data sensitive? Could you upload a sample workbook? (The board has that functionality if you go into the Advanced reply mode.)