+ Reply to Thread
Results 1 to 2 of 2

Conditional formatting handle issue with VBA

  1. #1
    Registered User
    Join Date
    03-25-2012
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    Excel 2007
    Posts
    12

    Angry Conditional formatting handle issue with VBA

    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

    First, this code checks whether there is any duplication of the formula.
    Then delete all same above conditional formatting formulas.
    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
    zANLA.jpg

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Conditional formatting handle issue with VBA

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new here, I will do it fir you today: https://www.mrexcel.com/board/thread...h-vba.1162360/)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Formatting Issue
    By ehizzlefosho in forum Excel General
    Replies: 3
    Last Post: 05-12-2016, 11:32 AM
  2. How to handle ties with conditional formatting
    By Julie8818 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-12-2015, 04:08 PM
  3. Not sure how to handle data table issue
    By Butcher1 in forum Excel General
    Replies: 1
    Last Post: 11-04-2014, 03:34 PM
  4. Custom formatting issue using conditional formatting
    By wrongway15 in forum Excel General
    Replies: 2
    Last Post: 07-13-2014, 03:18 PM
  5. Conditional Formatting issue
    By roenajoyave in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 07-12-2013, 12:00 PM
  6. Replies: 1
    Last Post: 02-09-2010, 10:58 PM
  7. Conditional formatting issue
    By gdi2k in forum Excel General
    Replies: 1
    Last Post: 10-10-2007, 04:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1