How do I keep Conditional Formatting from a sheet when inserting a pivot table

[emmykav]

Hello, I have conditional formatting on an Excel sheet, but when I try to pivot on the data, I lose my conditional formatting. Is there a way o keep it?

 

[Dr. Demento]

My solution is to create a template with the CF formatting and then apply it with the following macro:

Sub paste_Formatting()
' ~~ Copy Sheets("Template") and paste CF/formatting to ActiveSheet
Dim wbk As Workbook
  Set wbk = ActiveWorkbook
Dim sht As Worksheet
  Set sht = ActiveSheet
Dim shtTempl As Worksheet
  Set shtTempl = wbk.Sheets("Template")
Dim rng As Range
  Set rng = ActiveCell
  
OptimizeVBA True
  If shtExists(shtTempl.Name, wbk) = False Then
    MsgBox "Template does not exists"
    Exit Sub
  End If
  
  ' ~~ Unhide Template worksheet
  shtTempl.Visible = xlSheetVisible
  ' ~~ Copy | Paste formatting from Template to ActiveSheet
  
  shtTempl.Cells.Copy
  sht.Cells(1, 1).PasteSpecial Paste:=xlPasteFormats
  rng.Select
  
  ' ~~ Hide Template worksheet (VeryHidden)
  shtTempl.Visible = xlSheetVeryHidden
OptimizeVBA False
End Sub

The OptimizeVBA code can be found at Guide to Improving VBA Performance. Faster Excel VBA
The shtExists code can be found at excel - Test or check if sheet exists - Stack Overflow (you'll need to change ThisWorkbook to ActiveWorkbook)

hth