Turn on printing in color in Excel VBA

Question

I have an inherited Excel spreadsheet that does a lot of work using VBA and then prints a page. Until now, it had color and printed in black and white, with the line in a sub:

.BlackAndWhite = True

Now the users want some highlighting in certain cases, and we have added colored conditional formatting. But I can't get the spreadsheet to print in color and show that highlighting.

I can see that the color is there because it pauses for me to print to PDF, but the saved PDF won't show the color. The users are printing to a color printer, and the printer is set up to print in color on the machine that this Excel exists. There is another version that must be run manually, and it prints in color.

Here is the printing code (which matches the one that prints in color, but I'm pretty sure the one that prints in color bypasses this altogether):

Public Sub Print_No_Interface()

With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .DisplayAlerts = False
    .EnableEvents = False
    .AutoRecover.Enabled = False
End With

And, at the end, when it prints, it calls this:

Public Sub Print_Helper()

With Worksheets("Detail").PageSetup
 .LeftMargin = Application.InchesToPoints(0.5)
 .RightMargin = Application.InchesToPoints(0.5)
 .TopMargin = Application.InchesToPoints(0.75)
 .BottomMargin = Application.InchesToPoints(1)
 .CenterHorizontally = True
 .Orientation = xlPortrait
 .FirstPageNumber = xlAutomatic
 .FitToPagesWide = 1
 .FitToPagesTall = 1
 .PrintArea = "A1:M118"
 .PaperSize = xlPaperLegal
 .PrintGridlines = True
 .Zoom = 50
 End With

Worksheets("Detail").PrintOut ActivePrinter:="OUR COLOR PRINTER"

End Sub

I tried with .BlackAndWhite = False, with commenting it out, and finally removing it. But it still prints only in black and white. How do I get it to print in color now?

Where else should I look that might be telling the page to print in black and white?

Unless I'm missing something, the Print_No_Interface sub does nothing regarding printing...Also check that the printer's default properties aren't set to Black and White. — BruceWayne
@BruceWayne ok on the sub - I'm kind of grasping at straws at what might be affecting this. And the printer's default property is set to color. But I'll check again. — thursdaysgeek
You should let them choose the color options manually with Application.Dialogs(xlDialogPrint).Show - otherwise, it's a huge pain. See here for more details. — dwirony
@dwirony The version that I'm trying to debug runs without any "them" involved. It's scheduled and runs and prints automatically, on a virtual machine, with no users. — thursdaysgeek
@thursdaysgeek Where are you checking your printer settings? Under Printer Properties in Excel? What do you see under Advanced - > Printer Features there? Is "Print All Text as Black" enabled or disabled? — dwirony

dwirony dwirony · Accepted Answer · 2019-02-26T22:35:51

You'll need to make sure that your printer settings are properly configured in Excel. You can change these by going to:

File -> Print -> Printer Properties.

Turn on printing in color in Excel VBA

1 Answers