Crashes: manual formatting overriding conditional formatting (using VBA custom formula)

%3CLINGO-SUB%20id%3D%22lingo-sub-1656406%22%20slang%3D%22en-US%22%3ECrashes%3A%20manual%20formatting%20overriding%20conditional%20formatting%20(using%20VBA%20custom%20formula)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1656406%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20override%20conditional%20formatting%20(which%20sets%20left%20border%20of%20a%20cell%20at%20a%20certain%20condition)%20when%20I%20manually%20format%20left%20border%20to%20anything.%20I%20have%20coded%20custom%20formula%20to%20detect%20if%20a%20cell%20has%20a%20left%20border%20using%20VBA.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPublic%20Function%20ISLEFTBORDERED(rng%20As%20Range)%20As%20Boolean%0A%20%20%20%20If%20rng.Borders.Item(xlEdgeLeft).LineStyle%20%26lt%3B%26gt%3B%20-4142%20Then%20ISLEFTBORDERED%20%3D%20True%0AEnd%20Function%0A%0APublic%20Function%20ISRIGHTBORDERED(rng%20As%20Range)%20As%20Boolean%0A%20%20%20%20If%20rng.Borders.Item(xlEdgeRight).LineStyle%20%26lt%3B%26gt%3B%20xlLineStyleNone%20Then%20ISRIGHTBORDERED%20%3D%20True%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAnd%20conditional%20formatting%20references%20this%20formula%20to%20figure%20out%20if%20condition%20is%20satisfied%20at%20cell%20B1%20for%20example%20and%20if%20the%20left%20border%20of%20cell%20B1%20should%20be%20set.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3D%20AND(NOT(ISLEFTBORDERED(B1))%2C%20NOT(ISRIGHTBORDERED(A1))%2C%20B1%20%26lt%3B%26gt%3B%20A1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EHowever%2C%20if%20I%20reapply%20the%20conditional%20formatting%2C%20the%20whole%20excel%20crashes.%20If%20not%20occasionally%2C%20manual%20format%20does%20not%20correctly%20override%20at%20some%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20I%20need%20to%20change%20either%20VBA%20or%20conditional%20formatting%20to%20prevent%20the%20crashes%3F%20If%20possible%2C%20I%20want%20to%20fix%20the%20cells%20where%20manual%20formatting%20still%20does%20not%20override%20conditional%20formatting.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1656406%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

I am trying to override conditional formatting (which sets left border of a cell at a certain condition) when I manually format left border to anything. I have coded custom formula to detect if a cell has a left border using VBA.

Public Function ISLEFTBORDERED(rng As Range) As Boolean
    If rng.Borders.Item(xlEdgeLeft).LineStyle <> -4142 Then ISLEFTBORDERED = True
End Function

Public Function ISRIGHTBORDERED(rng As Range) As Boolean
    If rng.Borders.Item(xlEdgeRight).LineStyle <> xlLineStyleNone Then ISRIGHTBORDERED = True
End Function

And conditional formatting references this formula to figure out if condition is satisfied at cell B1 for example and if the left border of cell B1 should be set.

= AND(NOT(ISLEFTBORDERED(B1)), NOT(ISRIGHTBORDERED(A1)), B1 <> A1)

However, if I reapply the conditional formatting, the whole excel crashes. If not occasionally, manual format does not correctly override at some cells.

 

Do I need to change either VBA or conditional formatting to prevent the crashes? If possible, I want to fix the cells where manual formatting still does not override conditional formatting.

0 Replies