VBA script takes multiple passes to fully work

[Beyond_avarice]

Hey Gang,

I have put together the following script to help me delete links that are hidden within ConditionalFormats. This is to break those links that the "Edit Links" wizard can't do.

It "works" but I have found that on some of my workbooks; I have to go through a couple passes to eliminate everything. One example took me four passes and the last two passes involved a range I don't see in the first two passes. I suspect that there is something going on with the "AppliestTo.Address".

Hopefully will pick on what I haven't been able to detect as the issue. Thank you in advance.

Sub condFormLinkBreak3()

Dim cRule As String                 'ConditionalFormat Rule
Dim cForm As Object                 'ConditionalFormat Range applied to
Dim asw As Worksheet
Dim z as Single                       'Counter for Txt detail

Application.ScreenUpdating = False

Set asw = ActiveSheet

Sheets.Add(After:=Sheets(Sheets.Count)).Name = "TXT"
'
asw.Activate

z = 1

For Each cForm In ActiveSheet.Cells().FormatConditions

    On Error Resume Next
    'Debug.Print cForm.Formula1
    'Debug.Print cForm.AppliesTo.Address
   
    Sheets("TXT").Range("A" & z).Value = cForm.AppliesTo.Address
    Sheets("TXT").Range("B" & z).Value = """" & cForm.Formula1 & """"
    z = z + 1
    
    On Error Resume Next
    cRule = ""
    cRule = cForm.Formula1
            If InStr(1, cRule, "[") > 0 Then
                   asw.Range(cForm.AppliesTo.Address).FormatConditions(1).Delete  'delete anything with "["
            End If
    
Next
   
Application.ScreenUpdating = True

End Sub

 

[Beyond_avarice]

Bump 1 of 2. Will make the 2nd and final bump on 8/23/17.

Thank you for any assistance you can give on helping me rectify this issue.

 

[Fluff]

Hia
Rather than a For Each loop try

For I = ActiveSheet.Cells().FormatConditions.Count To 1 Step -1

 

[Beyond_avarice]

Hia
Rather than a For Each loop try

For I = ActiveSheet.Cells().FormatConditions.Count To 1 Step -1

I'm not sure I understand why this would be different and I'm not sure how to integrate what is a count of cells that have FormatConditions, as perhaps an "index" of what cells contain that ConditionFormat to then identify if that Cell has a "[" component that I am looking for; to determine if that ConditionalFormat should be deleted.

 

[Beyond_avarice]

Ok I've identified the "Item" method of FormatConditions. I'm digging into it. I see the formula, I'm still looking for a Cell reference.

Think I found it. Putting it together and seeing what happens.

x = Cells().FormatConditions().Item(i).Formula1      'This gives me the ConditionalFormat formula that I can pass through the inStr().

y = Cells().FormatConditions().Item(i).AppliesTo.Address

 

[Beyond_avarice]

Well I got that to work in two passes instead of four. I should probably re-experiment with my previous script and see if it does the same. I am again generating the same new "AppliesTo.Address" after performing the first pass.

I'm not sure how deleting existing ConditionalFormats; causes a new(er) AppliesTo.Address. I've gone through the first run and only see two ranges(multi range AppliesTo) that would apply but the rest I can't make sense of; even comparing number or rows and columns between these ranges with others.

 

[Fluff]

My suspicion, is that when using the for each loop, if you delete the 2nd FormatConditions the 3rd FormatConditions will become the 2nd & therefore wont get checked. Hence why you would be getting new ranges.

 

[Beyond_avarice]

@Fluff,

Thank you for your help. I lined up my first run and second run, to identify what changed; I found out that I had multiple conditional formats with the same "AppliesTo.Address". Because some of the "AppliesTo.Address" are listed before the item that contains the linked formula. My script would delete those first address and not actually the ConditionalFormat that contained the string of "[" that I was looking for and thus those same links would remain until I reran the script.

With your suggestion; the specific index of ConditionalFormat can be tested and exclusively deleted. Again, Thank you for your help. For anybody with ConditionalFormat link issues that might find this useful:

Sub condFormLinkBreak4()

Dim cRule As String, cAp2 As String      'ConditionalFormat Rule and Applies to address
Dim aWS As Worksheet

Application.ScreenUpdating = False

Set aWS = ActiveSheet
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "TXT"
aWS.Activate

z = 1

For i = ActiveSheet.Cells().FormatConditions.Count To 1 Step -1
    On Error Resume Next
     cRule = Cells().FormatConditions().Item(i).Formula1
     cAp2 = Cells().FormatConditions().Item(i).AppliesTo.Address
               
   
    Sheets("TXT").Range("A" & z).Value = cAp2
    Sheets("TXT").Range("B" & z).Value = """" & cRule & """"
    
    z = z + 1
    
    On Error Resume Next
    
            If InStr(1, cRule, "[") > 0 Then
                  aWS.Cells().FormatConditions().Item(i).Delete        'delete anything with "["
            End If
    
Next
   
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Glad to be able to help & thanks for the feedback