Conditional Formatting

**efernandes67** · 12-12-2010, 04:20 PM

Hi,

I am using Excel 2007 and I have created several conditional formatting rules through the use of VBA.

Using VBA:
1) How can I delete a specific rule (not all of them)?

2) How can I retrieve a list of all rules in a range?

Regards,
Elio Fernandes

**Leith Ross** · 12-12-2010, 06:52 PM

Hello Elio,

Here is macro that will display the conditional formats for the active cell if they exist. The can be expanded to include other formats like the Font, Border, Pattern, etc.

Sub ShowConditionalFormats()

  Dim CF As FormatCondition
  Dim I As Long
  Dim Msg As String
  Dim Rng As Range
    
    Set Rng = ActiveCell
  
    For I = 1 To Rng.FormatConditions.Count
        On Error Resume Next
          Set CF = Rng.FormatConditions(I)
          If Err = 0 Then GoSub GetConditions
        On Error GoTo 0
    Next I
    
Exit Sub

GetConditions:

      If CF.Type = 1 Then Msg = "Cell Value Is " Else Msg = "Formula Is "
      
      F1 = CF.Formula1
      
      On Error Resume Next
        F2 = CF.Formula2
        If Err <> 0 Then F2 = ""
      On Error GoTo 0
      
      Select Case CF.Operator
        Case 1: Msg = Msg & "Between " & F1 & " and " & F2
        Case 2: Msg = Msg & "Not Between " & F1 & " and " & F2
        Case 3: Msg = Msg & "Equal To " & F1
        Case 4: Msg = Msg & "Not Equal To " & F1
        Case 5: Msg = Msg & "Greater Than " & F1
        Case 6: Msg = Msg & "Less Than " & F1
        Case 7: Msg = Msg & "Greater Than Or Equal To " & F1
        Case 8: Msg = Msg & "Less Than Or Equal To " & F1
      End Select
      
      Msg = Msg & vbCrLf & "Format Color = " & CF.Interior.ColorIndex
    
      MsgBox Msg, vbOKOnly, "Condition " & I
      
Return

End Sub

To delete a specific condition use its index number. The example below deletes condition 3 from the active cell.

    With ActiveCell.FormatConditions(3).Delete

**efernandes67** · 12-13-2010, 06:57 AM

Hello Ross,

Thanks again for your help.

I just attached a file because I am still having problems deleting specific "FormatConditions".

The code in the module is:

Sub delSpecificRules()
    Dim Rng As Range
    Dim r, nRules
    
    Set Rng = Range("B22:AJ44")
    
    nRules = Rng.FormatConditions.Count
    
    For r = 15 To nRules
        With Rng.FormatConditions(r).Delete
        End With
    Next
End Sub

I have created 19 rules, and I need to change my code, so that I can delete from the 15th untill the last one.

Regards,
Elio Fernandes