CODEWITHSUNDEEP
excelvba
Eliyahu
Eliyahu

Reputation: 158

How to use VBA to remove 1 (most recent) conditional formatting rule, while leaving the rest intact

I have an Excel spreadsheet with some in built conditional formatting that I don't want to touch.

I've got a macro to add a color scale (Excel calls it the "Red - Yellow - Green" color scale) to a selection, which I use simply as a diagnostic. I want to be able to quickly remove that rule while leaving the others intact. The macro recorder is not accomplishing this effectively.

Upvotes: 0

Views: 5696

Answers (2)

Mark Forsyth
Mark Forsyth

Reputation: 1

Tried above but it got confused so it works if you delete from the last one backwards. This deletes them all:

For i = conditions To 1 Step -1  
 Selection.FormatConditions(i).Delete
Next i

Upvotes: 0

Sven Rojek
Sven Rojek

Reputation: 5838

You have to determine which Format was first. After that you can delete the FormatConditions Item()

Sub Remove_Second_Format()

    With Selection
            .FormatConditions.Item(2).Delete
    End With

End Sub

Update:

If your conditinal types are different, you could check for the type and delete all rules that equal that type. Select your cells and run this macro to remove all "xlExpression" Types

Sub Remove_Second_Format()

    conditions = Selection.FormatConditions.Count
    For i = 1 To conditions
        If Selection.FormatConditions(i).Type = xlExpression Then
            Selection.FormatConditions(i).Delete
        End If
    Next i
End Sub

There a serveral types like: ColorScale, DataBar, xlExpression - you must be more specific in what you really use... mind to give some code?

Upvotes: 2

Related Questions