Printing a workbook without colour

O

oggie1205

New member
Joined
Apr 8, 2013
Messages
6
Reaction score
0
Points
0
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.
 
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.
 
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?

NoS said:
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.
Click to expand...
 
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.
 
Thanks Ken, much appreciated.

Learn something new every day.
 
Happy to! That's what I love about the forum world. There's almost always an alternate way to accomplish a goal. :)
 
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?
 
Hi,

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

Thanks again for your help with this.

NoS said:
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?
Click to expand...
 
Oggie, is the data sensitive? Could you upload a sample workbook? (The board has that functionality if you go into the Advanced reply mode.)
 
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

Ken Puls said:
Oggie, is the data sensitive? Could you upload a sample workbook? (The board has that functionality if you go into the Advanced reply mode.)
Click to expand...
 

Attachments

  • ATHENA.xlsm
    338.3 KB · Views: 17
Hi oggie,

I've loaded the VBA, created the Control Panel worksheet (you can hide this once you've verified it works), and update the first two worksheets for you. Give those two a print (not print preview) and make sure they're working as you want.

If you check the conditional formats on those sheets you'll see how to convert your conditional formats from "blanks" to use a formula. From there, my recommendations would be this:
  • Wipe the rules on your other worksheets
  • Start with the first cell on the sheet that needs the format (and only that single cell)
  • Create a new conditional format and use a formula to determine the cells
  • Enter the formula in the following format: =AND(LEN(celladdress)=0,rngPrintMode="No")
  • Make sure you change the formula so that it refers to only the first cell in the range (you have a lot of merged cells in here) and make sure it doesn't have dollar signs in it
  • Commit the formula and make sure it works
  • Go back to Manage Rules, click in the Applies to box, press comma and add the other ranges you want to apply it to, each separated by a comma
  • If the rule won't apply it's because you've tried to apply it to too many cells at once. Select less cells in the applies to area and make a new rule to do the others. (I had to do this on your Details sheet, so Section C, D and E-G's rules are actually 3 separate rules.

It's really important to wipe your rule set and not try to modify the existing rules. Multi-cell rules tend to hold the reference for the first cell they were created from, which can be REALLY confusing. Follow the steps above and you should be good.
 

Attachments

  • ATHENA.xlsm
    345.9 KB · Views: 18
oggie

The password protection is preventing the macro from doing its' thing.

Assuming you have the same password on ALL the sheets, you could try changing the loop through the sheets to this, using your password instead of excel123 between the quotes.

Code: 
    For sht = 1 To Sheets.Count
        With Sheets(sht)
            .Unprotect "excel123"
            .Cells.FormatConditions.Delete
            .Protect "excel123"
        End With
    Next sht
It's quick and easy to do but you have to be really careful NOT TO SAVE the file after running this.

Ken's method would be much better in the long run.
 
You must log in or register to reply here.
Back
Top