Fonction Texte()

Function Fond(t$, coul$)
Application.Volatile
Fond = t
End Function

Function Polic(t$, coul$)
Application.Volatile
Polic = t
End Function

Private Sub Worksheet_Calculate()
Dim c As Range
Cells.Interior.ColorIndex = xlNone: Cells.Font.ColorIndex = xlAutomatic 'RAZ facultatif
For Each c In Me.UsedRange
  If c.Formula Like "=Fond(*rouge*)" Then c.Interior.ColorIndex = 3
  If c.Formula Like "=Fond(*vert*)" Then c.Interior.ColorIndex = 4
  If c.Formula Like "=Fond(*bleu*)" Then c.Interior.ColorIndex = 33
  If c.Formula Like "=Fond(*jaune*)" Then c.Interior.ColorIndex = 6
  If c.Formula Like "=Polic(*rouge*)" Then c.Font.ColorIndex = 3
  If c.Formula Like "=Polic(*vert*)" Then c.Font.ColorIndex = 4
  If c.Formula Like "=Polic(*bleu*)" Then c.Font.ColorIndex = 33
  If c.Formula Like "=Polic(*jaune*)" Then c.Font.ColorIndex = 6
Next
End Sub

Private Sub Worksheet_Calculate()
Dim a, c As Range, test1 As Boolean, test2 As Boolean, coul As Variant
a = Evaluate("{""rouge"",3;""vert"",4;""bleu"",33;""jaune"",6}")
Cells.Interior.ColorIndex = xlNone: Cells.Font.ColorIndex = xlAutomatic 'RAZ facultatif
For Each c In Me.UsedRange
  test1 = c.Formula Like "=Fond(*,*)"
  If Not test1 Then test2 = c.Formula Like "=Polic(*,*)"
  If test1 Or test2 Then
    coul = Evaluate(Replace(Split(c.Formula, ",")(1), ")", ""))
    coul = Application.VLookup(coul, a, 2, 0) 'RECHERCHEV
    If IsNumeric(coul) Then _
      If test1 Then c.Interior.ColorIndex = coul Else c.Font.ColorIndex = coul
  End If
Next
End Sub

Function FormatNombre(n, format$)
FormatNombre = IIf(n = "", "", n)
End Function

Option Compare Text 'la casse est ignorée

Private Sub Worksheet_Calculate()
Dim c As Range, t$
On Error Resume Next 'si le format n'est pas reconnu
For Each c In Me.UsedRange
  If c.Formula Like "=FormatNombre(*,*)" Then
    c.NumberFormat = "General" 'RAZ
    t = Mid(c.Formula, InStr(c.Formula, ",") + 1)
    t = Evaluate(Replace(t, ")", "")) 'valeur du 2ème argument
    t = Replace(t, ",", ".")
    t = Replace(t, " ", ",") 'séparateur des milliers
    t = Replace(t, "Rouge", "Red") 'traduction
    t = Replace(t, "Bleu", "Blue")
    t = Replace(t, "Vert", "Green")
    c.NumberFormat = t
  End If
Next
End Sub