Restore cell color after print

powerman

New Member
Joined
Jan 2, 2003
Messages
7
hi, i am using excel 2003. i have a form, inside has several cells for key in data, let's say B2, C5 and D2. i fill the cell with yellow color.
i dont want yelllow color to be print out, and after print out the yellow color still remain there.
i try

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cells.Interior.ColorIndex = xlNone
End Sub

but after print, yellow color cannot restore.

thanks for help.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
Powerman,
All i do is highligh what cells I don't want printed in colour, go to "Fill Colour" and select No colour.

Print document and then go "undo"

This takes the sheet back to where I was before printing

Pedro
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
try something like
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean) 
Dim x As Range, r As Range
With ActiveSheet
   For Each r In ActiveSheet.UsedRange
      If r.Interior.ColorIndex = 6 Then
         If x Is Nothing Then
            Set x = r
         Else
            Set x = Union(x,r)
         End If
      End If
   Next
   .Cells.Interior.ColorIndex = xlNone 
   'PrintCodeHere

   If Not x Is Nothing Then x.Interior.ColorIndex = 6
End with
End Sub
 

powerman

New Member
Joined
Jan 2, 2003
Messages
7
search and found Mr.Evik wrote before:

If you have still got "room" to make conditional formating.
On the sheet to be printed name a cell: "printing" (or whatever)
give all the cells (of your printarea) a conditional format: IF(printing=1) and set the colors the way you want
in my workbook this is attached to a button, but you can make it work in a Private Sub with some modifications
Code:
Sub print_sheet()
Range("printing") = 1
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("printing") = 0
End Sub

still dont know how to do......:<

still trying how to do conditional format
 

powerman

New Member
Joined
Jan 2, 2003
Messages
7

ADVERTISEMENT

hi, mr.jindon. using your code, after print preview, the color (light yellow) didnt restore.
 

powerman

New Member
Joined
Jan 2, 2003
Messages
7

ADVERTISEMENT

Oh.....i just copy and paste what you have posted just now. I dont know the code at all :>

just now i use a blank sheet, in one cell fill in yellow color, then copy your code in VBE, then run print preview.

any problem ? need to modify anything ?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
then try this one
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean) 
Dim x As Range, r As Range
With ActiveSheet
   For Each r In ActiveSheet.UsedRange
      If r.Interior.ColorIndex = 6 Then
         If x Is Nothing Then
            Set x = r
         Else
            Set x = Union(x,r)
         End If
      End If
   Next
   .Cells.Interior.ColorIndex = xlNone 
   .PrintOut
   If Not x Is Nothing Then x.Interior.ColorIndex = 6
End with
End Sub
 

powerman

New Member
Joined
Jan 2, 2003
Messages
7
hurray....thanks mr.jindon. it works. but after print preview, i click close, it return to print preview again (instead of go back normal view), i need to click close one more time then it go back normal view. why ?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
The code is just a sample...

You need to modify to suite...

Or
Delete current code
Paste the code to Standard Module
Run "RunBeforePrint" before print and "RunAfterPrint"
Code:
Dim x As Range, wsName As String

Sub RunBeforePrint()
Dim r As Range
With ActiveSheet
   wsName = .Name
   For Each r In .UsedRange
      If r.Interior.ColorIndex = 6 Then
         If x Is Nothing Then
            Set x = r
         Else
            Set x = Union(x,r)
         End If
      End If
   Next
   If Not x Is Nothing Then x.Interior.ColorIndex = xlNone
End With
End Sub

Sub RunAfterPrint()
If Not x Is Nothing Then
   If wsName = "" Then wsName = ActiveSheet.Name
   Sheets(wsName).x.Interior.ColorIndex = 6
End If
End Sub
 

Forum statistics

Threads
1,136,988
Messages
5,678,969
Members
419,796
Latest member
doctorgresham

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