Clear and restore conditional formatting of different cells before printing

[Retroshift]

Hi VBA-experts,
I'm looking for a working code to clear conditional formatting of different cells before printing and restore them after printing. The macro should run automatically when I want to print the specific worksheet and also restore the conditional formatting after printing. The removal of the conditional formatting cells should be visible on the print preview of the Excel document.

I had an idea for some basic code but it is far from complete. (no use of buttons or datavalidation please and the final document is printed in color (but not the cleared conditional formatting cells))
Thanks



Private Sub Workbook_BeforePrint(Cancel As Boolean)


Sheets("Sheet1").Range("B6,B10").FormatConditions.Delete

End Sub

[mana]

Thisworkbook module


Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)

If ActiveSheet.Name = "Sheet1" Then
Application.OnTime Now(), "ThisWorkbook.myPrint"
Cancel = True
End If

End Sub


Sub myPrint()

ActiveSheet.Copy before:=Sheets(1)
With Sheets(1)
.Cells.FormatConditions.Delete
Application.EnableEvents = False
.PrintOut
Application.EnableEvents = True
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

End Sub

[Retroshift]

Hey Mana, thanks for your reply. Your code seems to work. However, I cannot see the changes in the print preview and it only prints correctly when I run the macro. I wanted to let the macro run automatically when I go to "File Menu" > "Print". How could I alter the code to do this?

[mana]

It's difficult.


Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim msg As String

If ActiveSheet.Name = "Sheet1" Then
msg = "PrintOut or Preview?"
msg = msg & vbCrLf & vbCrLf & "Yes:PrintOut"
msg = msg & vbCrLf & "No:Preview"
If MsgBox(msg, vbYesNo) = vbYes Then
Application.OnTime Now(), "ThisWorkbook.myPrint"
Cancel = True
End If
End If

End Sub