Remove cell color when print

[rusa31]

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

Print Preview

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.

 

[GlennUK]

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]

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.

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]

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]

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.

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.

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.

Sub AfterPrint()

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

End Sub

Thank you again.

 

[yky]

Glad to help and thanks for the feedback.

 

[CorrieAnn]

I used the code above and attached it to a control on my worksheet called "Contacts".

Sub CONTACTS_Print()

Sheets("Contacts").Range("A1:BF36").Interior.Color = RGB(255, 255, 255)
Sheets("Contacts").Range("A1:BF36").Font.Color = RGB(0, 0, 0)

Application.OnTime Now, "CONTACTS_AfterPrint"

End Sub

Sub CONTACTS_AfterPrint()

Sheets("Contacts").Range("A1:BF36").Interior.Color = RGB(0, 0, 0)
Sheets("Contacts").Range("A1:AM36").Font.Color = RGB(217, 217, 217)
Sheets("Contacts").Range("BF1:BF36").Font.Color = RGB(109, 182, 255)
    Range("A1").Select
End Sub

Unfortunately, nothing happens when I invoke the code. What am I doing wrong??

 

[yky]

It works for me. I changed the code so it worked on the ActiveSheet. Nothing else was changed. After running the first part of the code, the cell background turned white. The second part turned the cell background to black.

Did you have the code in one or two controls? You need two controls, one for the first part of the code, which turns the cell background to white, another for the second part of the code, which turns the cell background to black. If you put both parts in one control, you won't see the effect of the first part of the code.

 

[CorrieAnn]

I have it all on one control. I don't want to see it happening. I just want the report/print area to print white background with black font. The print properties and margins for the worksheet are already set. I then need to reset the on-screen view to black background with grey font. Everyone in the office works in dark mode, so the tools I build for them are formatted with a black fill. However, I do not wish for it to print this way if/when printing is needed. Hence my need to revert back to standard black and white printing.

I just want one button that says PRINT which will handle all the steps on one click.