Remove Cell Colour Before Printing

[Nigel_cro]

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

 

[Nigel_cro]

Rory,

Brilliant!

Except, stranger than a strange thing, the coloured cells now come out of the printer shaded grey!!

Nigel

 

[HippoCampus]

Hi Nigel

I am not the Worlds best at Macro's but I just experimented in recording one and it did the job. The first time you do it, it may be labour intensive depending on the amount of shaed cells that you have but heres what I did, with record macro on

1. selected all of the cells that were highlighted
2. Removed the shading
3. Pressed print
4. As all of the cells were still selected I just then added the shading back in
5. Put a button on the form, cleverly named unshade_print_shade
6. Made the button properties not print

and it worked fine, this is how my macro recorded

Sub Unshade_Print_Shade()
'
' Unshade_Print_Shade Macro
' Macro recorded 16/03/2009 by HippoCampus
'
' Keyboard Shortcut: Ctrl+l
'
Range("C1:C4").Select
Selection.Interior.ColorIndex = xlNone
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub

 

[RoryA]

Nigel,
That is odd, as it does not do that for me. Which version of Excel are you on?