VBA: how to to Clear Contents of a Range After Applying Conditional Formatting
I'm having trouble with a VBA macro that clears the contents of a specified range after applying conditional formatting to it. My intention is to format the cells based on certain criteria and then clear them for a new dataset, but it seems that the conditional formatting rules are persisting even after I clear the cells. Here’s the relevant part of my code: ```vba Sub FormatAndClearCells() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Data") ' Apply conditional formatting With ws.Range("A1:A10") .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="10" .FormatConditions(1).Interior.Color = RGB(255, 0, 0) ' Red fill for values greater than 10 End With ' Clear contents ws.Range("A1:A10").ClearContents End Sub ``` After running this macro, I expect the cells A1:A10 to be empty, but the conditional formatting rule seems to stay active. If I manually check the format rules, it still shows the rule I applied. Is there a way to ensure that the conditional formatting is removed when I clear the contents? I also tried using `.FormatConditions.Delete` after clearing the contents, like this: ```vba ws.Range("A1:A10").ClearContents ws.Range("A1:A10").FormatConditions.Delete ``` However, this still doesn’t seem to work as expected. I'm using Excel 2019 and I've verified that the range syntax is correct. Any suggestions on how to resolve this scenario or properly reset the formatting after clearing the contents would be greatly appreciated!