Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Printing a workbook without colour

  1. #1

    Printing a workbook without colour



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.

  3. #3
    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?

    Quote Originally Posted by NoS View Post
    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.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Thanks Ken, much appreciated.

    Learn something new every day.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Happy to! That's what I love about the forum world. There's almost always an alternate way to accomplish a goal.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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?

  8. #8
    Hi,

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

    Thanks again for your help with this.

    Quote Originally Posted by NoS View Post
    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?

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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 Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    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

    Quote Originally Posted by Ken Puls View Post
    Oggie, is the data sensitive? Could you upload a sample workbook? (The board has that functionality if you go into the Advanced reply mode.)
    Attached Files Attached Files

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •