Unlimited Conditional Formatting

Introduction

The limitation of MFC (Conditional Formatting) to Excel version 2007 is less than 3. This is very short. Having seen many applications to push the limit, I am amused to write a macro to do it, without needing to change it. It operates on demand as required.

Principle

The macro will retrieve the formats to be applied in a sheet named MFC, cell A1 of that sheet is used to apply the default format.
The macro is triggered by entering a cell, it uses the MFC on the cell. But to apply the formats on the cell and, not having to change the macro to define the different beaches on the program, a CFP in the cell with a formula: = Ma_MFC.
This macro can also conduct formats according to levels of values.

Code

Here is the code to be placed in the ThisWorkbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim i As Integer, j As Long, Mfc As FormatCondition, c As Range, Ws1 As Worksheet
    On Error GoTo fin ' en cas de mauvaise manipulation, ça plante sur l'ordre suivant
    Application.EnableEvents = False
    Set Ws1 = Sheets("MFC")
    For i = 1 To Target.FormatConditions.Count
        Set Mfc = Target.FormatConditions(i)
        If UCase(Left(Mfc.Formula1, 7)) = "=MA_MFC" Then
            Ws1.Range("A1").Value = Target.Value
            Set c = Nothing
            For j = 2 To Ws1.Range("A65536").End(xlUp).Row
                If Ws1.Range("A" & j) = True Then
                    Set c = Ws1.Range("A" & j)
                    Exit For
                End If
            Next j
            If c Is Nothing Then Set c = Ws1.Range("A1")
            c.Copy
            Target.PasteSpecial (xlPasteFormats)
            Application.CutCopyMode = False
        End If
    Next i
    Application.EnableEvents = True
fin:
    On Error GoTo 0
End Sub