Remove cell color when print

rusa31

New Member
Joined
Jan 31, 2021
Messages
13
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Hello,

I need help.
I wanted to remove the some cell color before printing.
Some cell I have put the color (Green) for the user to know where to enter input.
When the user enter "Ctrl+P", the Green cell will be removed.

Below is the table :-

Before Print

1613067217670.png


Print Preview

1613067388899.png


Info :-
1) Remove the Green color when printing (Ctrl+P).
2) Not all cell with green color user will enter input. however, still need to remove the green when printing (ctrl+P).
3) This excel will used by basic user which they would not know about run Macro or VBA.

Sample Excel (Download): excel_Example.xlsx

I'm not sure if this possible to do. or maybe it is too complex to do it.
Appreciate if you can give any feedback.

Thank you. :)
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Why don't you set a colour style for those cells, instead of colouring individually (sure, you have to apply the style that way, once, but then things get easier). Then you adjust the style .... change colour to None, and then print, then change back again.
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,835
Office Version
  1. 2010
Platform
  1. Windows
Is this a fixed spreadsheet, meaning it won't change? If so, you can add a BeforePrint event and save it as an .xlsm or .xlsb file.

VBA Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)

Sheets(1).Range("C4:H9").Interior.Color = RGB(255, 255, 255)
'free to add more ranges

End Sub

Note: I didn't really print it to paper. I printed it a PDF file and the color in C4:H9 was gone though the color persisted in the preview window.

If this is not a fixed spreadsheet, you'll need to loop through every cell and change color.
 

rusa31

New Member
Joined
Jan 31, 2021
Messages
13
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Why don't you set a colour style for those cells, instead of colouring individually (sure, you have to apply the style that way, once, but then things get easier). Then you adjust the style .... change colour to None, and then print, then change back again.

Thank you Glenn for the idea.
 

rusa31

New Member
Joined
Jan 31, 2021
Messages
13
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Is this a fixed spreadsheet, meaning it won't change? If so, you can add a BeforePrint event and save it as an .xlsm or .xlsb file.

VBA Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)

Sheets(1).Range("C4:H9").Interior.Color = RGB(255, 255, 255)
'free to add more ranges

End Sub

Note: I didn't really print it to paper. I printed it a PDF file and the color in C4:H9 was gone though the color persisted in the preview window.

If this is not a fixed spreadsheet, you'll need to loop through every cell and change color.

Hi yky,

Thank you so much.
Your code works. I just add another code to return back to the original color.

VBA Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)

Sheets(1).Range("C4:H9").Interior.Color = RGB(255, 255, 255)
'free to add more ranges

Application.Ontime Now, "AfterPrint"
'Return back to original color

End Sub

Then I add another code in other module. To return back to the original color.

VBA Code:
Sub AfterPrint()

Sheets(1).Range("C4:H9").Interior.Color = RGB(166, 166, 166)

End Sub

Thank you again. :)
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,835
Office Version
  1. 2010
Platform
  1. Windows
Glad to help and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,107
Messages
5,628,729
Members
416,333
Latest member
Time2Learn

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
Top