Restore cell color after print

[powerman]

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.

 

[pedro-egoli]

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]

Hi
try something like

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]

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]

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

 

[jindon]

Do you want to show us your code?

 

[powerman]

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]

then try this one

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]

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]

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"

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