Hello Dear Excel Masters
The thing which bothers me is: How do I remove or reference particular formating?
a)
I can not use index to access particular item of FormatConditions collection, because the position in collection is volatile.
b)
I can not filter FormatConditions collection by address of .AppliesTo.Address because more than 1 condition can apply to same range.
c) I can not use named range because it is translated in FormatConditions to range.
Here is example:
I create a named range like this:
Dim colo As String
Dim rng As Range
Dim expr As String
colo="MyRng"
Set rng=Range("A1")
expr="=A1=1"
With Range(colo).FormatConditions.Add(xlExpression, Formula1:=expr)
.Interior.Color = 100000
.StopIfTrue = False
End With
How do I remove the condtion such that other conditions stay?
Following code removes all conditions.
'########################################
'# Remove all Cformating in current sheet
Public Sub remCformat(Optional rng As Range)
Dim cf As FormatCondition
Dim rn As Range
If Not rng Is Nothing Then
Set rn = rng
Else
Set rn = ActiveSheet.Cells
End If
On Error Resume Next
For Each cf In rn.FormatConditions
Debug.Print cformatType(cf.Type), cf.Formula1, "deleted"
cf.Delete
Next cf
End Sub
Edit:
Ah I see I have to filter both by AppliesTo.Address and by expresion.
The conditional formating i such a pain. I dont like it but it is much faster than applying formats stored in arrays.
Bookmarks