Macro to not print shaded cells

[Ecsailer]

I am looking for a step by step process to creat a macro that will not print shaded cells but still print in color. Does anyone have a spreadsheet with this macro so I can just copy it? I need step by step info on how to do this and where to put the macro.

[HaHoBe]

Hi, Ecsailer,

maybe you can adapt Invisible Ink instead of using the font the fill.

Ciao,
Holger

[Ecsailer]

I did everything but I cannot figure out where to put the code. I typed Alt F11 but nothing comes up. More help please.

[HaHoBe]

Hi, Ecsailer,

the code belongs in ThisWorkbook, and I changed it to just use the PrintPreview instead of PrintOut:

Please Login or Register  to view this content.

Please note that you have to apply the Format to the cells before trying to print - have a look at the attached workbook.

Ciao,
Hoilger

[Ecsailer]

Everything worked well. Thank you. I had to modify the code a bit. A few more questions, when I try and protect the sheet it gives me an error. How do I correct this? Also, if I want the shading to be other than grey, like light green, how do I do that? Below is the code I modified a bit.


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
Cancel = True
With Me.Styles("PrintWhite")
.Font.Color = RGB(31, 73, 125)
With .Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End With
ActiveWindow.SelectedSheets.PrintPreview
With Me.Styles("PrintWhite")
.Font.Color = RGB(31, 73, 125)
With .Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.049946592608417
.PatternTintAndShade = 0
End With
End With
Application.EnableEvents = True
End Sub

[HaHoBe]

Hi, Ecsailer,

as the code will change the color it will need permission to do so. Generally spoken there are two ways: unprotect the sheet before print, print and protect afterwards or protect with teh parameter UserInterfaceOnly:=True.

Please Login or Register  to view this content.

Regarding the colours: best way to get the colours would be to record a macro while doing so and take the code from there.

Please use code-tags when you display code here.

Ciao,
Holger

[Ecsailer]

Holger,

Thank you so much for your help. I have learned alot from your advise. I do still have one problem however. The code works fine prior to the inclusion of the Protect Sheet commands. I get an error when trying to use that part of the code. I have tried to record a code and I still get the error. I attached the file I am trying to work on. The green shaded cells are what I want to print white.

Run-time Error: '1004':

Application-defined or object-defined error

Any thoughts on what I am doing wrong?

Copy (2) of D261604 - FAS (Description), LINxxxx.xls

[HaHoBe]

Hi, Ecsailer,

testing the workbook I ran into problems with Sheets("Daily Equipment") as the line

Please Login or Register  to view this content.

breaks the code. At present I donŽt see the forest just because of teh trees it seems.

I altered the code to read

Please Login or Register  to view this content.

but still got an error and couldnŽt print that sheet. IŽll think IŽll have a closer look a little bit later.

Ciao,
Holger

[Ecsailer]

Thats the error I got too. But that line of code does not appear to be a problem when the ActiveSheet.protect code lines are omitted. Thanks for helping.