Conditional formatting handle/delete issue with VBA

mohsinbipu

New Member
Joined
Feb 19, 2021
Messages
17
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Web
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-
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
 
Last edited by a moderator:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Maybe you can change the formula with: cfFormula = "=OR(CELL(""col"")=COLUMN(),CELL(""row"")=ROW())"

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

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top