convert conditional formatting into standard one

[keks]

hello guys i feel this board is tyhe last resort for this question - is anyone aware of any add-in that could do this

many many many many thankyou's for help

this relates to ms excel 2003 only

alex

 

[sspatriots]

Hi,

I didn't get any responses to my post yesterday, so I kept digging until I found something close. The problem I have now is with the code below, it will not Quit MS Word, because it wants me to respond to the question "You placed a large amount of content on the Clipboard. Do you want this content to be available to other applications after you quit Word?" No matter what I do to the macro, it will not get passed this alert and quit. Any help greatly appreciated. Thanks.

Sub CopyCondFmt2WordThenBackSoKeepsFmtButLosesCondFmtEquations()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
'*** Purpose: retain conditional formatting in an Excel range but LOSE the conditional formatting "equations"<o></o>
'*** Note: In Excel VBA, turn on "tools-references-MicrosoftWordxxObjectLibrary"<o></o>
'*** 1) Copies Excel range to Word via the clipboard. NOTE: the range has conditional formating<o></o>
'*** 2) Opens Word and pastes the Excel data into a new Word doc (as HTML)<o></o>
'*** 3) copies this same data in word, placing it in the clipboard (HTML format)<o></o>
'<o></o>
<o> </o>
'*** (1) copy Excel range<o></o>
<o> </o>
Application.DisplayClipboardWindow = True '***watch clipboard action<o></o>
Application.DisplayAlerts = False<o></o>
Application.CutCopyMode = False '***clear copy (ergo, clear clipboard)<o></o>
<o> </o>
<o></o>
Cells.Select<o></o>
Selection.EntireColumn.Hidden = False<o></o>
Range("A1").Select<o></o>
<o> </o>
<o> </o>
Range("A1:Q2003").Select<o></o>
Selection.Copy '***copy the excel area<o></o>
<o> </o>
<o> </o>
' (2) now commands apply to Word = "appWD"<o></o>
<o> </o>
<o> </o>
Set appWD = CreateObject("Word.Application") '***create word object<o></o>
appWD.Application.DisplayAlerts = False<o></o>
appWD.Visible = True '***show word<o></o>
appWD.Documents.Add '***new document<o></o>
appWD.WordBasic.EditOfficeClipboard '***watch clipboard in Word also<o></o>
appWD.Selection.PasteExcelTable False, False, False '***paste the Excel data<o></o>
appWD.Selection.WholeStory '*** (3) select the same data and<o></o>
appWD.Selection.Copy '*** copy it to clipboard in an HTML format<o></o>
appWD.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges<o></o>
appWD.DisplayAlerts = wdAlertsNone 'Turn off alerts<o></o>
appWD.Quit<o></o>
Set appWD = Nothing<o></o>
<o> </o>
<o> </o>
' **** back to excel<o></o>
<o> </o>
'ActiveWorkbook.Worksheets.Add<o></o>
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MySheet"<o></o>
<o> </o>
<o> </o>
Range("A1").Select '***area to paste data as HTML<o></o>
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False 'paste from word*End Sub<o></o>
'Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False<o></o>
<o> </o>
<o> </o>
Range("A1").Select<o></o>
Application.CutCopyMode = False<o></o>
<o> </o>
<o> </o>
Application.CommandBars("Office Clipboard").Visible = False<o></o>
<o> </o>
<o> </o>
Application.DisplayAlerts = True<o></o>
<o> </o>
<o> </o>
End Sub<o></o>

 

[Patejl]

Hi again

As I told you by PM the code was, in fact, only dealing with Background Colour and Font Colour, those are the ones I usually use.

Since you are also using Borders in your Conditional formatting, this was a good ocasion to add them. I took the opportunity to also add the rest: Font Style, Underline and Strikethrough.
...

Thank you for the script. I've made couple of modifications to fix compatibility issues with Excel 2007:

' PGC Nov 06
' Delinks formats from conditions in cells with conditional formatting.
' The cells keep the format that was enabled with the conditional formatting
' but as normal format. Background and Font Colour
' Oct 07
' Added Borders, Font Style, Underline and Strikethrough
' Patejl Feb 12
' Fixed Excel 2007 compatibility issues

Sub ConditionalFormatDelink(rRng As Range)
Dim vConditionsSyntax, rCell As Range, rCFormat As Range, iCondition As Integer
Dim sFormula As String, vCSyntax, vOperator, iBorder As Integer, vBorders
Dim firstRow As Long, firstColumn As Long, firstCell As Range, conditionArea As Range

vBorders = Array(xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlEdgeBottom)

' Syntax for "Value is" Conditions
vConditionsSyntax = Array( _
    Array(xlEqual, "CellRef = Condition1"), _
    Array(xlNotEqual, "CellRef <> Condition1"), _
    Array(xlLess, "CellRef < Condition1"), _
    Array(xlLessEqual, "CellRef <= Condition1"), _
    Array(xlGreater, "CellRef > Condition1"), _
    Array(xlGreaterEqual, "CellRef >= Condition1"), _
    Array(xlBetween, "AND(CellRef >= Condition1, CellRef <= Condition2)"), _
    Array(xlNotBetween, "OR(CellRef < Condition1, CellRef > Condition2)") _
)

' Get cells with format
On Error GoTo EndSub
Set rCFormat = rRng.SpecialCells(xlCellTypeAllFormatConditions)

On Error Resume Next
For Each rCell In rCFormat ' Loops through all the cells with conditional formatting
    If Not IsError(rCell) Then ' skips cells with error
        rCell.Activate
        With rCell.FormatConditions
            For iCondition = 1 To .Count ' loops through all the conditions
                
                'Locate the first cell in the AppliesTo area (used as a reference for sFormula)
                firstRow = .Item(iCondition).AppliesTo.Row
                firstColumn = .Item(iCondition).AppliesTo.Column
                
                If .Item(iCondition).AppliesTo.Areas.Count > 1 Then
                    For Each conditionArea In .Item(iCondition).AppliesTo.Areas
                        If conditionArea.Row < firstRow Then firstRow = conditionArea.Row
                        If conditionArea.Column < firstColumn Then firstColumn = conditionArea.Column
                    Next conditionArea
                End If

                Set firstCell = Cells(firstRow, firstColumn)
                
                sFormula = .Item(iCondition).Formula1
                Err.Clear
                vOperator = .Item(iCondition).Operator
                If Err <> 0 Then ' "Formula Is"
                    Err.Clear
                Else ' "Value Is"
                    For Each vCSyntax In vConditionsSyntax ' checks all the condition types
                        If .Item(iCondition).Operator = vCSyntax(0) Then
                            ' build the formula equivalent to the condition
                            sFormula = Replace(vCSyntax(1), "Condition1", Evaluate(sFormula))
                            sFormula = Replace(sFormula, "CellRef", rCell.Address)
                            sFormula = Replace(sFormula, "Condition2", Evaluate(.Item(iCondition).Formula2))
                            Exit For
                        End If
                    Next vCSyntax
                End If
                
                'Uncomment when needed: Fix formula (Czech list separator -> US list separator)
                'sFormula = Replace(sFormula, ",", ".")
                'sFormula = Replace(sFormula, ";", ",")
                
                'Shift formula (relate it to the first cell of the AppliesTo area)
                sFormula = Application.ConvertFormula(Formula:=sFormula, fromReferenceStyle:=xlA1, toReferenceStyle:=xlR1C1, RelativeTo:=firstCell)
                sFormula = Application.ConvertFormula(Formula:=sFormula, fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1, RelativeTo:=rCell)
                
                If Evaluate(sFormula) Then
                    ' The cell has a condition = True. Delink the format from the conditional formatting
                    
                    ' Background
                    If Not IsNull(.Item(iCondition).Interior.ColorIndex) Then _
                        rCell.Interior.Color = .Item(iCondition).Interior.Color
                    
                    ' Font
                    If Not IsNull(.Item(iCondition).Font.ColorIndex) Then _
                        rCell.Font.Color = .Item(iCondition).Font.Color
                    If Not IsNull(.Item(iCondition).Font.FontStyle) Then _
                        rCell.Font.FontStyle = .Item(iCondition).Font.FontStyle
                    If Not IsNull(.Item(iCondition).Font.Strikethrough) Then _
                        rCell.Font.Strikethrough = .Item(iCondition).Font.Strikethrough
                    If Not IsNull(.Item(iCondition).Font.Underline) Then _
                        rCell.Font.Underline = .Item(iCondition).Font.Underline
                    
                    ' Borders
                    With .Item(iCondition)
                        For iBorder = 1 To 4
                            If .Borders(iBorder).LineStyle <> xlNone Then
                                rCell.Borders(vBorders(iBorder - 1)).LineStyle = .Borders(iBorder).LineStyle
                                rCell.Borders(vBorders(iBorder - 1)).ColorIndex = .Borders(iBorder).ColorIndex
                                rCell.Borders(vBorders(iBorder - 1)).Weight = .Borders(iBorder).Weight
                            End If
                        Next iBorder
                    End With
                    
                    'Exit on StopIfTrue
                    If .Item(iCondition).StopIfTrue Then Exit For
                End If
            Next iCondition
        End With
    End If
    rCell.FormatConditions.Delete ' deletes the cell's conditional formatting
Next rCell
EndSub:
End Sub

 

[Sergthegod]

Hello!

I am new to the forum and desperatly ask for help.

The fact is that I use the code of pgc01, and it works fine, but not for me.

The situation is that my conditional formatting uses, actually, four arguments:

x is target
If the value of the cell is 5% higher than x - the formatting is green
If the value of the cell is x or higher less than 5% - the formatting is not set
If the value of the cell is less than 5% lower than x - the formatting is yellow
If the value of the cell is 5% and more lower than x - the formatting is red

Sadly, I can't change those conditions.

As the result - the code is almost random - it sets the first condition correctly, making the conditional green - static green, the other cells just become the colour the second condition. Suggests.

Please, I am obviously stupid, I am unable to teach the following code the third condition and the "not set" condition.

Sub ConditionalFormatDelink(rRng As Range)
Dim vConditionsSyntax, rCell As Range, rCFormat As Range, iCondition As Integer
Dim sFormula As String, vCSyntax, vOperator, iBorder As Integer, vBorders
Dim firstRow As Long, firstColumn As Long, firstCell As Range, conditionArea As Range


vBorders = Array(xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlEdgeBottom)


' Syntax for "Value is" Conditions
vConditionsSyntax = Array( _
    Array(xlEqual, "CellRef = Condition1"), _
    Array(xlNotEqual, "CellRef <> Condition1"), _
    Array(xlLess, "CellRef < Condition1"), _
    Array(xlLessEqual, "CellRef <= Condition1"), _
    Array(xlGreater, "CellRef > Condition1"), _
    Array(xlGreaterEqual, "CellRef >= Condition1"), _
    Array(xlBetween, "AND(CellRef >= Condition1, CellRef <= Condition2)"), _
    Array(xlNotBetween, "OR(CellRef < Condition1, CellRef > Condition2)") _
)


' Get cells with format
On Error GoTo EndSub
Set rCFormat = rRng.SpecialCells(xlCellTypeAllFormatConditions)


On Error Resume Next
For Each rCell In rCFormat ' Loops through all the cells with conditional formatting
    If Not IsError(rCell) Then ' skips cells with error
        rCell.Activate
        With rCell.FormatConditions
            For iCondition = 1 To .Count ' loops through all the conditions
                
                'Locate the first cell in the AppliesTo area (used as a reference for sFormula)
                firstRow = .Item(iCondition).AppliesTo.Row
                firstColumn = .Item(iCondition).AppliesTo.Column
                
                If .Item(iCondition).AppliesTo.Areas.Count > 1 Then
                    For Each conditionArea In .Item(iCondition).AppliesTo.Areas
                        If conditionArea.Row < firstRow Then firstRow = conditionArea.Row
                        If conditionArea.Column < firstColumn Then firstColumn = conditionArea.Column
                    Next conditionArea
                End If


                Set firstCell = Cells(firstRow, firstColumn)
                
                sFormula = .Item(iCondition).Formula1
                Err.Clear
                vOperator = .Item(iCondition).Operator
                If Err <> 0 Then ' "Formula Is"
                    Err.Clear
                Else ' "Value Is"
                    For Each vCSyntax In vConditionsSyntax ' checks all the condition types
                        If .Item(iCondition).Operator = vCSyntax(0) Then
                            ' build the formula equivalent to the condition
                            sFormula = Replace(vCSyntax(1), "Condition1", Evaluate(sFormula))
                            sFormula = Replace(sFormula, "CellRef", rCell.Address)
                            sFormula = Replace(sFormula, "Condition2", Evaluate(.Item(iCondition).Formula2))
                            Exit For
                        End If
                    Next vCSyntax
                End If
                
                'Shift formula (relate it to the first cell of the AppliesTo area)
                sFormula = Application.ConvertFormula(Formula:=sFormula, fromReferenceStyle:=xlA1, toReferenceStyle:=xlR1C1, RelativeTo:=firstCell)
                sFormula = Application.ConvertFormula(Formula:=sFormula, fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1, RelativeTo:=rCell)
                
                If Evaluate(sFormula) Then
                    ' The cell has a condition = True. Delink the format from the conditional formatting
                    
                    ' Background
                    If Not IsNull(.Item(iCondition).Interior.ColorIndex) Then _
                        rCell.Interior.Color = .Item(iCondition).Interior.Color


                    'Exit on StopIfTrue
                    If .Item(iCondition).StopIfTrue Then Exit For
                End If
            Next iCondition
        End With
    End If
    rCell.FormatConditions.Delete ' deletes the cell's conditional formatting
Next rCell
EndSub:
End Sub

 

[AndyBasic]

Re: convert conditional formatting into standard one - Add Stop if True check

Hi, this was just what I was looking for as well. It works great for condition 1, but it "erases" condition 2. My condition 1 was made permanent as desired, but my conditions 2 just went away.

    'Menu items (with their set of Rights) that did not exist on the last report are highlighted in YELLOW (6)
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AA2="""""
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ColorIndex = 6
        .TintAndShade = 0
    End With
    
    'Rights that have changed from last report are highlighted in RED (3)
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AA2<>E2"
    With Selection.FormatConditions(2).Interior
        .PatternColorIndex = xlAutomatic
        .ColorIndex = 3
    End With

Please advise. Thank you.

I hit this problem where there is multiple Conditional Formating on one cell - the code published in this thread exits after processing the first Conditional Formatting rule i.e. It assumes that the "Stop If True" option is set. Unfortunately adding a check to see if this is set, and continuing to the next rule if it isn't, doesn't work when one rule has a fill colour for the cell and a later one has "No Colour" as fill colour. In this case the conditional formatting seems to ignore the "No Colour" and uses the colour from the previous CF rule. I have enhanced the code to 1. Check the 'Stop If True" flag and 2. To ignore "No Colour" when this is specified as the fill or thr font colour. It is possible that there are other issues with allowing multiple conditional format rules to apply which I have encountered in my workbooks.

New version of code -

Sub ConditionalFormatDelink(rRng As Range)

' Converts conditional formating to actual formatting of the cells specified

' Call E.g.   Call ConditionalFormatDelink(Range("J3:IJ3"))

' PGC Nov 06
' Delinks formats from conditions in cells with conditional formatting.
' The cells keep the format that was enabled with the conditional formatting
' but as normal format. Background and Font Colour
' Oct 07
' Added Borders, Font Style, Underline and Strikethrough
' Apr 16
' Add check of 'Stop If True' condition

Dim vConditionsSyntax, rCell As Range, rCFormat As Range, iCondition As Integer
Dim sFormula As String, vCSyntax, vOperator, iBorder As Integer, vBorders

vBorders = Array(xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlEdgeBottom)

' Syntax for "Value is" Conditions
vConditionsSyntax = Array( _
    Array(xlEqual, "CellRef = Condition1"), _
    Array(xlNotEqual, "CellRef <> Condition1"), _
    Array(xlLess, "CellRef < Condition1"), _
    Array(xlLessEqual, "CellRef <= Condition1"), _
    Array(xlGreater, "CellRef > Condition1"), _
    Array(xlGreaterEqual, "CellRef >= Condition1"), _
    Array(xlBetween, "AND(CellRef >= Condition1, CellRef <= Condition2)"), _
    Array(xlNotBetween, "OR(CellRef < Condition1, CellRef > Condition2)") _
)

' Get cells with format
On Error GoTo EndSub
Set rCFormat = rRng.SpecialCells(xlCellTypeAllFormatConditions)

On Error Resume Next
For Each rCell In rCFormat ' Loops through all the cells with conditional formatting
    If Not IsError(rCell) Then ' skips cells with error
        rCell.Activate
        With rCell.FormatConditions
            For iCondition = 1 To .Count ' loops through all the conditions
                sFormula = .Item(iCondition).Formula1
                Err.Clear
                vOperator = .Item(iCondition).Operator
                If Err <> 0 Then ' "Formula Is"
                    Err.Clear
                Else ' "Value Is"
                    For Each vCSyntax In vConditionsSyntax ' checks all the condition types
                        If .Item(iCondition).Operator = vCSyntax(0) Then
                            ' build the formula equivalent to the condition
                            sFormula = Replace(vCSyntax(1), "Condition1", sFormula)
                            sFormula = Replace(sFormula, "CellRef", rCell.Address)
                            sFormula = Replace(sFormula, "Condition2", .Item(iCondition).Formula2)
                            Exit For
                        End If
                    Next vCSyntax
                End If
                If Evaluate(sFormula) Then
                    ' The cell has a condition = True. Delink the format from the conditional formatting
                    
                    ' Background - If a colour is specified and it's not 'No Colour'
                    If Not IsNull(.Item(iCondition).Interior.ColorIndex) Then
                        If .Item(iCondition).Interior.ColorIndex <> -4142 Then _
                            rCell.Interior.ColorIndex = .Item(iCondition).Interior.ColorIndex
                    End If
                    
                    ' Font
                    If Not IsNull(.Item(iCondition).Font.ColorIndex) Then
                        If .Item(iCondition).Interior.ColorIndex <> -4142 Then _
                            rCell.Font.ColorIndex = .Item(iCondition).Font.ColorIndex
                    End If
                    If Not IsNull(.Item(iCondition).Font.FontStyle) Then _
                        rCell.Font.FontStyle = .Item(iCondition).Font.FontStyle
                    If Not IsNull(.Item(iCondition).Font.Strikethrough) Then _
                        rCell.Font.Strikethrough = .Item(iCondition).Font.Strikethrough
                    If Not IsNull(.Item(iCondition).Font.Underline) Then _
                        rCell.Font.Underline = .Item(iCondition).Font.Underline
                    
                    ' Borders
                    With .Item(iCondition)
                        For iBorder = 1 To 4
                            If .Borders(iBorder).LineStyle <> xlNone Then
                                rCell.Borders(vBorders(iBorder - 1)).LineStyle = .Borders(iBorder).LineStyle
                                rCell.Borders(vBorders(iBorder - 1)).ColorIndex = .Borders(iBorder).ColorIndex
                                rCell.Borders(vBorders(iBorder - 1)).Weight = .Borders(iBorder).Weight
                            End If
                        Next iBorder
                    End With
                    If .Item(iCondition).StopIfTrue Then
                        Exit For ' if one condition is true skips the next ones
                    End If
                End If
            Next iCondition
        End With
    End If
    rCell.FormatConditions.Delete ' deletes the cell's conditional formatting
Next rCell
EndSub:
End Sub

 

[sharpeye]

Hi PGC
I was looking for this exact same solution as this as my speadsheet crashes a lot due to the amount of data and the amount of conditional formatting i have used and I was hoping by removing the formatting it may help to stop the crashes.
Your VBA code works to perfection so Im currently removing all my conditional formatting, I have a really good feeling that will help a lot.
Many Many thanks
Ian

 

[acharnp]

Hi again

As I told you by PM the code was, in fact, only dealing with Background Colour and Font Colour, those are the ones I usually use.

Since you are also using Borders in your Conditional formatting, this was a good ocasion to add them. I took the opportunity to also add the rest: Font Style, Underline and Strikethrough.

Try:

' PGC Nov 06
' Delinks formats from conditions in cells with conditional formatting.
' The cells keep the format that was enabled with the conditional formatting
' but as normal format. Background and Font Colour
' Oct 07
' Added Borders, Font Style, Underline and Strikethrough
Sub ConditionalFormatDelink(rRng As Range)
Dim vConditionsSyntax, rCell As Range, rCFormat As Range, iCondition As Integer
Dim sFormula As String, vCSyntax, vOperator, iBorder As Integer, vBorders

vBorders = Array(xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlEdgeBottom)

' Syntax for "Value is" Conditions
vConditionsSyntax = Array( _
    Array(xlEqual, "CellRef = Condition1"), _
    Array(xlNotEqual, "CellRef <> Condition1"), _
    Array(xlLess, "CellRef < Condition1"), _
    Array(xlLessEqual, "CellRef <= Condition1"), _
    Array(xlGreater, "CellRef > Condition1"), _
    Array(xlGreaterEqual, "CellRef >= Condition1"), _
    Array(xlBetween, "AND(CellRef >= Condition1, CellRef <= Condition2)"), _
    Array(xlNotBetween, "OR(CellRef < Condition1, CellRef > Condition2)") _
)

' Get cells with format
On Error GoTo EndSub
Set rCFormat = rRng.SpecialCells(xlCellTypeAllFormatConditions)

On Error Resume Next
For Each rCell In rCFormat ' Loops through all the cells with conditional formatting
    If Not IsError(rCell) Then ' skips cells with error
        rCell.Activate
        With rCell.FormatConditions
            For iCondition = 1 To .Count ' loops through all the conditions
                sFormula = .Item(iCondition).Formula1
                Err.Clear
                vOperator = .Item(iCondition).Operator
                If Err <> 0 Then ' "Formula Is"
                    Err.Clear
                Else ' "Value Is"
                    For Each vCSyntax In vConditionsSyntax ' checks all the condition types
                        If .Item(iCondition).Operator = vCSyntax(0) Then
                            ' build the formula equivalent to the condition
                            sFormula = Replace(vCSyntax(1), "Condition1", sFormula)
                            sFormula = Replace(sFormula, "CellRef", rCell.Address)
                            sFormula = Replace(sFormula, "Condition2", .Item(iCondition).Formula2)
                            Exit For
                        End If
                    Next vCSyntax
                End If
                If Evaluate(sFormula) Then
                    ' The cell has a condition = True. Delink the format from the conditional formatting
                   
                    ' Background
                    If Not IsNull(.Item(iCondition).Interior.ColorIndex) Then _
                        rCell.Interior.ColorIndex = .Item(iCondition).Interior.ColorIndex
                   
                    ' Font
                    If Not IsNull(.Item(iCondition).Font.ColorIndex) Then _
                        rCell.Font.ColorIndex = .Item(iCondition).Font.ColorIndex
                    If Not IsNull(.Item(iCondition).Font.FontStyle) Then _
                        rCell.Font.FontStyle = .Item(iCondition).Font.FontStyle
                    If Not IsNull(.Item(iCondition).Font.Strikethrough) Then _
                        rCell.Font.Strikethrough = .Item(iCondition).Font.Strikethrough
                    If Not IsNull(.Item(iCondition).Font.Underline) Then _
                        rCell.Font.Underline = .Item(iCondition).Font.Underline
                   
                    ' Borders
                    With .Item(iCondition)
                        For iBorder = 1 To 4
                            If .Borders(iBorder).LineStyle <> xlNone Then
                                rCell.Borders(vBorders(iBorder - 1)).LineStyle = .Borders(iBorder).LineStyle
                                rCell.Borders(vBorders(iBorder - 1)).ColorIndex = .Borders(iBorder).ColorIndex
                                rCell.Borders(vBorders(iBorder - 1)).Weight = .Borders(iBorder).Weight
                            End If
                        Next iBorder
                    End With
                    Exit For ' if one condition is true skips the next ones
                End If
            Next iCondition
        End With
    End If
    rCell.FormatConditions.Delete ' deletes the cell's conditional formatting
Next rCell
EndSub:
End Sub

Call it with (don't forget to ajust the range):

Sub BreakCF()
   Call ConditionalFormatDelink(Range("J3:IJ3"))
End Sub

Run the code in the active sheet.

Very nice solution. Thanks a lot for the VBA solution.
I also made another small Subroutine only to Remove all Conditional Formats for selected cells