How do I use VBA to delete only certain conditional formatting rules applying to a range?

[whereswaller]

I am using VBA code to add a conditional formatting rule to a dynamically selected column of cells in a worksheet (specified by SelectedCol variable). This is working satisfactorily (see below Code Extract).

I, however, would also like to use VBA code to remove this conditional formatting from a dynamically selected column of cells in a worksheet (using SelectedCol variable) when desired. I do not know how to select the appropriate conditional formatting rule for deletion once I've located the corresponding cell range. Can anyone help and/or share code that does this?

Note, I cannot simply remove all conditional formatting rules as there are other conditional formatting rules that also apply to the same cell range.

Code Extract:

[COLOR=#222222][FONT=Verdana]Sub AddRemovePublicHoliday()
[/FONT][/COLOR][/FONT][/COLOR]Dim FindString As String
    Dim Rng As Range
    Dim ColNum As Long
    Dim SelectedCol As String
    FindString = Range("B2")


    If (Trim(Range("B1")) <> "" And Trim(FindString) <> "") Then


        If Trim(Range("B1")) = "Add Public Holiday" Then


            With Sheets("Leave Register").Range("D2:ND2") 'searches range
             Set Rng = .Find(What:=DateValue(FindString), _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlFormulas, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False, _
                    SearchFormat:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True  'value found
                ColNum = ActiveCell.Column
                Dim vArr
                vArr = Split(Cells(1, ColNum).Address(True, False), "$")
                SelectedCol = vArr(0)


                MsgBox SelectedCol


                 Columns(SelectedCol & ":" & SelectedCol).FormatConditions.Add Type:=xlExpression, Formula1:= _
                 "=ROW()>2"
                    Columns(SelectedCol & ":" & SelectedCol).FormatConditions(Columns(SelectedCol & ":" & SelectedCol).FormatConditions.Count).SetFirstPriority
                    With Columns(SelectedCol & ":" & SelectedCol).FormatConditions(1).Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = vbBlack
                        .TintAndShade = 0
                    End With
                    With Columns(SelectedCol & ":" & SelectedCol).FormatConditions(1).Font
                        .Color = vbWhite
                        .TintAndShade = 0
                    End With


                Range("A1:A2").Select
                Sheets("Add-Remove Public Holiday").Select
                Range("B1").Select
                Application.CutCopyMode = False
                Selection.ClearContents
                Range("B2").Select
                Selection.ClearContents
                Range("A1").Select


                MsgBox "Public holiday on " & FindString & " added."


            Else


                MsgBox "Form incomplete. Cannot add or remove public holiday1."


            End If


        End With


    ElseIf Trim(Range("B1")) = "Remove Public Holiday" Then


    Sheets("Leave Register").Select




    ' code to delete specific conditional formatting


        MsgBox "Public holiday on " & FindString & " removed."


    Else


        MsgBox "Form incomplete. Cannot add or remove public holiday2."


    End If


    Else


    MsgBox "Form incomplete. Cannot add or remove public holiday3."


    End If
[COLOR=#242729][FONT=Arial][COLOR=#222222][FONT=Verdana]    End Sub[/FONT][/COLOR][/FONT][/COLOR]

 

[rlv01]

The general approach is something like this.

                    'Adding a format condition
		    Dim FC As FormatCondition
                    Set FC = Columns(SelectedCol & ":" & SelectedCol).FormatConditions.Add(Type:=xlExpression, Formula1:="=ROW()>2")

                    With FC
                        .SetFirstPriority
                        With .Interior
                            .PatternColorIndex = xlAutomatic
                            .Color = vbBlack
                            .TintAndShade = 0
                        End With

                        With .Font
                            .Color = vbWhite
                            .TintAndShade = 0
                        End With
                    End With

            ' code to delete specific conditional formatting
            With Columns(SelectedCol & ":" & SelectedCol)
                For Each FC In .FormatConditions
                    If FC.Type = xlExpression And (FC.Formula1 = "=ROW()>2") Then
                        FC.Delete
                    End If
                Next FC
            End With

 

[dcha]

on behalf of the OP, thank you for the solution. It was very helpful.