Conditional formatting handle/delete issue with VBA

[mohsinbipu]

What I did: I am trying to insert below conditional formatting formula in every sheet of activeworkbook with VBA.

=OR(CELL("col")=COLUMN(),CELL("row")=ROW())

I used the below VBA code to apply the above conditional formatting in every sheet with one click. this code do below steps

1. First, this code checks whether there is any duplication of the formula.
2. Then delete all same above conditional formatting formulas.
3. then include a fresh above formula in every sheets.

Here is my code-

Sub InsertHighlighRowClmn()
Application.ScreenUpdating = False
Application.Calculation = xlManual
'On Error Resume Next

    'defining and set variable for workbook and sheets
    Dim wb As Workbook
    Dim ShtCount As Integer
    Set wb = ActiveWorkbook
    ShtCount = ActiveWorkbook.Sheets.Count
   
    'Defining the formula as a string
    Dim cfFormula As String
    cfFormula = "=OR(CELL(" & Chr(34) & "col" & Chr(34) & ")=COLUMN(),CELL(" & Chr(34) & "row" & Chr(34) & ")=ROW())"
   
    'defining a variable as formatcondition
    Dim cf As FormatCondition
       
    'deleteing formula if duplicate in a sheet.
    Dim i As Long
    For i = 1 To ShtCount
        For Each cf In wb.Sheets(i).Cells.FormatConditions
            If cf.Formula1 = cfFormula Then
                cf.Delete
            End If
        Next cf
    Next
   
    'applying fresh formula in every sheet
    For i = 1 To ShtCount
        wb.Sheets(i).Cells.FormatConditions.Add Type:=xlExpression, Formula1:=cfFormula
        wb.Sheets(i).Cells.FormatConditions(wb.Sheets(i).Cells.FormatConditions.Count).Interior.Color = RGB(255, 219, 219)4
    Next
   
Set wb = Nothing
Set cf = Nothing

End Sub

Where is my bad luck: I was randomly checking this formula with many conditions/formulas to find whether it creates any error or not. It is working fine except for one issue. When the same 02 cells are having this formula, then this code is giving me an error.

Runtime error -2147417848 Method delete of object FormatCondition failed

And when I reset the VBA code and then run again this code I am getting the below error and the excel file does not work; it freezes totally. I have attached a picture describing all issues. I studied a lot to find out the problem, but my bad luck. Is there any way I can solve this?

Runtime error 7; Out of memory

Also asked here Conditional formatting handle issue with VBA

 

[mart37]

Maybe you can change the formula with: cfFormula = "=OR(CELL(""col"")=COLUMN(),CELL(""row"")=ROW())"

On the end:
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic