Conditional formatting handle/delete issue with VBA

mohsinbipu

New Member
Joined
Feb 19, 2021
Messages
5
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:

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,059
Office Version
  1. 2016
Platform
  1. Windows
Maybe you can change the formula with: cfFormula = "=OR(CELL(""col"")=COLUMN(),CELL(""row"")=ROW())"

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

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

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
Top