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

Question

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

[Workbook.BeforeClose event](https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.beforeclose). — BigBen, Jan 10 '20 at 18:10
@BigBen I edited my question based on the link you shared (plz see the new code), but somehow it didn't work — Beans, Jan 10 '20 at 18:16

score 2 · Accepted Answer · answered Jan 10 '20 at 18:31

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

1 Answers1