macro to retain the conditional formatting result but to remove the rules behind

[eassy]

hi,

I'm enhancing my report which using a lot of conditional formatting (CF) rules and it causes increasing in the file size and slowness in opening the file.

i found a code, but it always read the CF as active whenever the cells had CF rules applied on it without checking whether the cells values met the conditional or not.

can anyone help to modify the code.

ex. cell (D:D) is the reference cell. if value for range (G7:L20) < reference cell, the font appear in red. otherwise, black.

Sub removeCF_2()
Dim asheet As Worksheet

Application.ScreenUpdating = False

For Each asheet In Worksheets
asheet.Activate
    Call ConditionalFormatDelink(Range("A1:AM133"))
Next asheet
End Sub

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


End Sub

[Winon]

Hello eassy,

Welcome to the Forum.

found a code, but it always read the CF as active whenever the cells had CF rules applied on it without checking whether the cells values met the conditional or not.

I checked it out on my Machine, and I cannot replicate your "problem". The Code works fine. Maybe your Data goes beyond the specified Range("A1:AM133"). If that's the case, change it accordingly.

[eassy]

the code seem working fine.. but i do not get the desired output..
ex: my reference value is 250.... when the cells value < 250, the font will be in red colour. whereas when it is greater than that, it will be in black (normal).. i just set one formatting rule that is when the cells value less than the reference cell, the font will be in red.

but when i ran the macro, all my cells font turn to red no matter the cells met the conditional rules or not....
that is my problem.

[Winon]

Then change this line:

' Get cells with format
On Error GoTo EndSub

to

' Get cells with format
On Error Resume Next

I have done that in the attached sample WorkBook, and it works fine.