CODEWITHSUNDEEP
excelvbaconditional-formatting
Michi
Michi

Reputation: 5471

Only delete a certain conditional formatting rule using VBA

My Excel spreadsheet consists of different conditional formatting:

VBA-Code:

Sub Apply_Conditional_Formatting()
With Sheet1.Range("=$A$1:$C$7")
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$A1=$F$1"
    .FormatConditions(.FormatConditions.Count).Interior.Color = RGB(255, 255, 0)
End With
End Sub

Conditioal Formatting Rules Manager:
(Sorry for only having it available in German)

enter image description here

Now I am looking for a VBA that identifies the second conditional formatting (the one that I apply with the VBA code above) and deletes it. The other conditional formatting should remain.

With the below VBA all conditional formatting rules get deleted:

Sub Delete_Conditional_Formatting()
sheet1.Range("$A$1:$C$7").FormatConditions.Delete
End Sub

What do I need to change in the code to only delete the second conditional formatting?

Upvotes: 1

Views: 2216

Answers (2)

VBasic2008
VBasic2008

Reputation: 54838

Identify Format Conditions (Conditional Formatting)

  • These two examples should shed a light on how to identify format conditions by their properties.

Links (Microsoft)

The Code

' Identified by One Property
' The following will delete all format conditions with yellow interior color,
' unless 'Exit For' is used.
Sub deleteYellow()
    Dim fc As FormatCondition
    For Each fc In Sheet1.Cells.FormatConditions
        If fc.Interior.Color = RGB(255, 255, 0) Then
            fc.Delete
            ' If you want to delete only the first or if there is only one
            ' format condition with yellow interior color then add:
            'Exit For
        End If
    Next fc
End Sub

' Identified by Two Properties
' The following will delete all format conditions with yellow interior color
' and with the AppliesTo address equal to "$A$1:$C$7",
' unless 'Exit For' is used.
Sub deleteYellowA1C7()
    Dim fc As FormatCondition
    For Each fc In Sheet1.Cells.FormatConditions
        If fc.Interior.Color = RGB(255, 255, 0) And _
           fc.AppliesTo.Address = "$A$1:$C$7" Then
            fc.Delete
            ' If you want to delete only the first or if there is only one,
            ' then add:
            'Exit For
        End If
    Next fc
End Sub

Upvotes: 1

BigBen
BigBen

Reputation: 50007

You can easily index into the FormatConditions collection and delete a specific one:

sheet1.Range("$A$1:$C$7").FormatConditions(2).Delete

Or if you want the last condition:

With Sheet1.Range("$A$1:$C$7")
    .FormatConditions(.FormatConditions.Count).Delete
End With

Upvotes: 1

Related Questions