excelvba

How to use VBA to remove conditional formatting when a workbook get closed

I understand I can use the code below to remove conditional formatting 

Sheets("PCAM Commitments").Activate
Cells.Select
Cells.FormatConditions.Delete

But what should I add to make it happens automatically when the workbook get closed every time?

----------UPDATE------

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("PCAM Commitments").Activate
Cells.Select
Cells.FormatConditions.Delete

End Sub

I tried this, but didn't work

Solution

  • This code should be located in the ThisWorkbook module:

    Also there is no need to Select/Activate - see this question:

    Finally, I would use the sheet codename:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheet1.Cells.FormatConditions.Delete 'Change the sheet as needed.
End Sub