VBA to turn off cell fill when printing then turn back on

[ydilday]

I am looking for a VBA to turn off cell fill when I go to print a spread sheet and then turn the fill back on after the printing is done.
Essentially I want the spread sheet to print in black and white but have the company logo print in color.
The cells are color filled with conditional formatting.
Thank you.

 

[6StringJazzer]

The fact that the formatting is conditional ramps up the difficulty. How many conditional formatting rules do you have?

The way I would approach this is to update the rules to include a "flag" cell. Let's say we use Z99 for that purpose. If I have a rule like this:

=A1>100

I would update it to

=AND(Z99, A1>100)

Then Z99 can be used like a switch to turn on and off the conditional formatting. (You could even use a checkbox linked to Z99 to do it.)

A macro can be installed to automatically make this cell FALSE before printing. However, there is no AfterPrint event to automatically turn it back on. You would have to use a macro to do the printing rather than File > Print.

 

[ydilday]

The fact that the formatting is conditional ramps up the difficulty. How many conditional formatting rules do you have?

The way I would approach this is to update the rules to include a "flag" cell. Let's say we use Z99 for that purpose. If I have a rule like this:

=A1>100

I would update it to

=AND(Z99, A1>100)

Then Z99 can be used like a switch to turn on and off the conditional formatting. (You could even use a checkbox linked to Z99 to do it.)

A macro can be installed to automatically make this cell FALSE before printing. However, there is no AfterPrint event to automatically turn it back on. You would have to use a macro to do the printing rather than File > Print.

I am fine with removing the conditional formatting and just using regular cell fill if that makes it easier.

 

[Joe4]

The fact that the formatting is conditional ramps up the difficulty. How many conditional formatting rules do you have?

The way I would approach this is to update the rules to include a "flag" cell. Let's say we use Z99 for that purpose. If I have a rule like this:

=A1>100

I would update it to

=AND(Z99, A1>100)

Then Z99 can be used like a switch to turn on and off the conditional formatting. (You could even use a checkbox linked to Z99 to do it.)

A macro can be installed to automatically make this cell FALSE before printing. However, there is no AfterPrint event to automatically turn it back on. You would have to use a macro to do the printing rather than File > Print.

Jeff, I like that approach! Very clever way to approach it!

 

[6StringJazzer]

I am fine with removing the conditional formatting and just using regular cell fill if that makes it easier.

Another way would be to create a copy of the sheet, remove all fill and conditional formatting, then print the copy and delete it. Note that it will remove all conditional formatting, including fonts and number formats.

Public Sub PrintNoFill()

   Dim PrintCopy As Worksheet
   Dim SourceWS As Worksheet
   
   Set SourceWS = ActiveSheet
   
   SourceWS.Copy after:=SourceWS
   Set PrintCopy = Worksheets(SourceWS.Index + 1)
   
   With PrintCopy.UsedRange.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
   End With
   
   PrintCopy.Cells.FormatConditions.Delete

   PrintCopy.PrintOut
   
   Application.DisplayAlerts = False
   PrintCopy.Delete
   Application.DisplayAlerts = True

End Sub