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

 

[pgc01]

Hi Alex

I've been using this code for a while with no problem.
You may want to give it a 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.
Sub ConditionalFormatDelink(rRng As Range)
Dim vConditionsSyntax, rCell As Range, rCFormat As Range, iCondition As Integer
Dim sFormula As String, vCSyntax, vOperator

' 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
                    rCell.Font.ColorIndex = .Item(iCondition).Font.ColorIndex
                    rCell.Interior.ColorIndex = .Item(iCondition).Interior.ColorIndex
                    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

To call it use something like:

Sub BreakCF()
   Call ConditionalFormatDelink(Range("A1:C10"))
End Sub

Run this code in the active sheet.

 

[keks]

it did work with a plain exaple, but unlikily does not with mine - it's rather sofisticated
unfortunately can't debug it to give you a better analysis

 

[pgc01]

Hi Alex

Thank you for the feedback.

I've been using this software for a while and, until now, I had no problem.

I'd like to improve it, and if it's possible that you send me a sample where the code fails I'd appreciate it.

I'll send you a PM with a mailbox address in case you want to do it.

Of course, you can mask the data and replace with dummy data if necessary, I really just want a case where the code does not work.

 

[pgc01]

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.

 

[keks]

pgc, huge thanks, it worked smoothelessly!!

 

[gardnertoo]

I love this forum! I searched for exactly this, and here it is!

 

[gazskeltz]

Hi

This doesn't seem to work for me - does it handle cells with threeconditional formats?

Gaz

 

[megnin]

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.

 

[sspatriots]

Hi,

I'm new to the forum and stumbled across this thread. I tried running the code and it colors all my cells in the range to the color of the condition and ignores the special condition Italics Bold font that I have set. I'm hoping someone can tell me what I might be doing wrong with the code.

Thanks,

Steve