Remove Cell Colour Before Printing

Nigel_cro

New Member
Joined
Mar 16, 2009
Messages
6
Hi Guys,

New here, but it seems like this is the place I need to be. Hopefully someone can help, I ihave been researching this for what seems like an age with no success.

I have a spreadsheet which is to be filled in by multiple users, in order to assist the users I have colour coded, and left unprotected, the cells that they need to interact with. I really don't want them messing with the formulae!

However, when a user chooses to print the sheet I would like the coloured cells to be printed in B&W.

I have a very little VBA knowledge - I know I need something to follow 'Private Sub Workbook_BeforePrint ()' or at least, I think I do!!

Any ideas??

Thanks in advance,

Nigel
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi Nigel

an easy way to achieve this would be to activate the drawing toolbar, draw a rectangle the same size as the cell, right click it to format it, colour it yellow, alter the transparency so that it is see through and in the properties tab unclick print.

Why though dont you lock all of the cells with the formula's in and leave the other boxes unlocked?

Cheers
 
Upvote 0
Hi HC,

Thanks for the swift response.

I hadn't even thought about the 'graphic' solution - that sounds like it will work. I always tend to look for the most complicated answer to any problem.

I have written a bit of VB to deal with a message box that opens when the SS is opened giving instructions to the user so I guess I just got carried away!

I am sure that there is a VB based answer in there somewhere I just can't get a start on it.

The SS is covered in formulae, DD boxes etc etc - the user only needs to access a very limited number of cells (I have protected the rest) the users of this SS will be of varying skill levels and I want to make the thing as user-friendly as I can - coloured cells seemed the way forward.

Thanks again,

Nigel
 
Upvote 0
HC,

Unless I am doing something incredibly stooopid (and there is always that possibility) the whole laying a coloured box over the top thing fails when you come to enter text into the cell.

Unless you either tab across or arrow across to the cell you can't actually enter data into it - this would be a little too confusing to some of my less technically minded users who would expect to click into the cell and type.

I think I need VB :(

Nigel
 
Upvote 0
Have you tried changing the Page Setup options to print Black and White?
 
Upvote 0
Rorya,

Thanks for the reply.

I had thought of this solution, but, I was unsure whether or not it was a protectable option, or whether it would return to default for each user that opens the SS - I guess I should have tested it??

EDIT - I really want to take away any possibility of choice from the user - this SS is in the way of being a standard form and really shouldn't be messed with - EDIT

I will give it a go, it could be the simplest answer yet!!

I would still like to know the VB answer if anyone has the time.........

BTW - looks like a nice day afloat, see you out there some time.
 
Last edited:
Upvote 0
Greetings,
I would agree with Rory that simply changing to B&W printing would be easiest. You could do this thru the before print event.

As to your question, here's a rather crude example of getting all the cells' interiors, changing to non-colored, printing and resetting the colors. I would note that if the same few cells are the only ones highlighted and these won't change, it would be faster to simply change them, print, change them back. Also - I did not take into account the sheet being protected, so add that. Anyways, for an example:

Hope this helps,

Mark

Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim _
rngPrint            As Range, _
strPrintArea        As String, _
lCols               As Long, _
lRows               As Long, _
lC                  As Long, _
lR                  As Long, _
aryRange()          As Long
    If ActiveSheet.Name = "MySheet" Then
 
        '// Cancel the called print//
        Cancel = True
 
        '// Get the current PrintArea//
        strPrintArea = ThisWorkbook.Worksheets("MySheet").PageSetup.PrintArea
 
        '// Set a reference to the same area//
        Set rngPrint = Range(strPrintArea)
 
        '// Find out how many columns and rows are in our print area, so    //
        '// that we can make a 2-dimensional array the same size            //
        lCols = rngPrint.Columns.Count
        lRows = rngPrint.Rows.Count
 
        '// Size the array//
        ReDim aryRange(1 To lCols, 1 To lRows)
 
        '// Store ea cell's current colorindex//
        For lR = 1 To lRows
            For lC = 1 To lCols
                aryRange(lC, lR) = rngPrint(lR, lC).Interior.ColorIndex
            Next
        Next
 
        '// REmove colors for prining//
        rngPrint.Interior.ColorIndex = -4142
 
        '// Kill events so that we don'r recurse, Print, reset events//
        Application.EnableEvents = False
        ActiveSheet.PrintOut
        Application.EnableEvents = True
 
        '// Re-color the same cells//
        For lR = 1 To lRows
            For lC = 1 To lCols
                rngPrint(lR, lC).Interior.ColorIndex = aryRange(lC, lR)
            Next
        Next
    End If
End Sub
 
Upvote 0
If you are going to use the BeforePrint (which will most likely muck up your ability to Print Preview), then I would suggest creating a specific style and applying that to the cells you want highlighted (you could include Protection settings too). Then all you need to do in the BeforePrint event is modify the style to use no fill, print, then put it back. The cell changes will be automatic that way.
 
Upvote 0
Thanks GTO,

Working my way through the code now - I think I understand what it is doing - I will have to play with it for a bit to get my head round it. The only scripting I have done has been with PERL, so this is all a bit new to me!

At the risk of taking up too much of your time - - How would I change to B&W printing using VB? this certainly would seem to be the easiest solution, although I like your 'crude' example!

All the best,

Nigel
 
Upvote 0
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
   Dim ws As Worksheet
   For Each ws In ThisWorkbook.Worksheets
      ws.PageSetup.BlackAndWhite = True
   Next ws
End Sub
should be OK, though if you have a lot of sheets, it won't be that quick.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top