Function CouleurMFC(cel) Application.Volatile Set c = Range(cel.Address) ff = Array("Somme", "aujourdhui()", "annee", "mois", "jour", "nb.si", "equiv", "recherchev", _ "Nbval", "sommeprod", "joursem", "gauche", "droite", "stxt", "trouve", "cherche") fa = Array("Sum", "today()", "year", "month", "day", "countif", "match", "vlookup", _ "counta", "sumproduct", "weekday", "left", "right", "mid", "find", "search") a = Array("=", ">", "<", ">=", "<=", "<>", "BETWEEN") b = Array(xlEqual, xlGreater, xlLess, xlGreaterEqual, xlLessEqual, xlNotEqual, xlBetween) i = 1 Do While i <= c.FormatConditions.Count And Not témoin If c.FormatConditions(i).Type = xlCellValue Then tmp1 = Evaluate(c.FormatConditions(i).Formula1) oper = a(Application.Match(c.FormatConditions(i).Operator, b, 0) - 1) If oper <> "BETWEEN" Then If Evaluate(c & oper & tmp1) Then coul = c.FormatConditions(i).Interior.ColorIndex témoin = True End If Else tmp2 = Evaluate(c.FormatConditions(i).Formula2) If Evaluate("AND(" & c & ">=" & tmp1 & "," & c & "<=" & tmp2 & ")") Then coul = c.FormatConditions(i).Interior.ColorIndex témoin = True End If End If Else z = c.FormatConditions(i).Formula1 For k = LBound(ff) To UBound(ff) z = Replace(z, UCase(ff(k)), UCase(fa(k))) Next k If Evaluate(z) = True Then coul = c.FormatConditions(i).Interior.ColorIndex témoin = True End If End If i = i + 1 Loop CouleurMFC = coul End Function
JB
On 25 jan, 17:07, magic-dd wrote:
salut
j'ai bien testé la combinaison qui permet de recuperer lacouleuret la valeur d'une cellule
or
est il possible de recuperer la valeur et lacouleurissue d'une mise en forme conditionelle
Function CouleurMFC(cel)
Application.Volatile
Set c = Range(cel.Address)
ff = Array("Somme", "aujourdhui()", "annee", "mois", "jour",
"nb.si", "equiv", "recherchev", _
"Nbval", "sommeprod", "joursem", "gauche", "droite", "stxt",
"trouve", "cherche")
fa = Array("Sum", "today()", "year", "month", "day", "countif",
"match", "vlookup", _
"counta", "sumproduct", "weekday", "left", "right", "mid", "find",
"search")
a = Array("=", ">", "<", ">=", "<=", "<>", "BETWEEN")
b = Array(xlEqual, xlGreater, xlLess, xlGreaterEqual, xlLessEqual,
xlNotEqual, xlBetween)
i = 1
Do While i <= c.FormatConditions.Count And Not témoin
If c.FormatConditions(i).Type = xlCellValue Then
tmp1 = Evaluate(c.FormatConditions(i).Formula1)
oper = a(Application.Match(c.FormatConditions(i).Operator, b, 0)
- 1)
If oper <> "BETWEEN" Then
If Evaluate(c & oper & tmp1) Then
coul = c.FormatConditions(i).Interior.ColorIndex
témoin = True
End If
Else
tmp2 = Evaluate(c.FormatConditions(i).Formula2)
If Evaluate("AND(" & c & ">=" & tmp1 & "," & c & "<=" & tmp2 &
")") Then
coul = c.FormatConditions(i).Interior.ColorIndex
témoin = True
End If
End If
Else
z = c.FormatConditions(i).Formula1
For k = LBound(ff) To UBound(ff)
z = Replace(z, UCase(ff(k)), UCase(fa(k)))
Next k
If Evaluate(z) = True Then
coul = c.FormatConditions(i).Interior.ColorIndex
témoin = True
End If
End If
i = i + 1
Loop
CouleurMFC = coul
End Function
JB
On 25 jan, 17:07, magic-dd <ciolan...@gmail.com> wrote:
salut
j'ai bien testé la combinaison qui permet de recuperer lacouleuret
la valeur d'une cellule
or
est il possible de recuperer la valeur et lacouleurissue d'une mise
en forme conditionelle
Function CouleurMFC(cel) Application.Volatile Set c = Range(cel.Address) ff = Array("Somme", "aujourdhui()", "annee", "mois", "jour", "nb.si", "equiv", "recherchev", _ "Nbval", "sommeprod", "joursem", "gauche", "droite", "stxt", "trouve", "cherche") fa = Array("Sum", "today()", "year", "month", "day", "countif", "match", "vlookup", _ "counta", "sumproduct", "weekday", "left", "right", "mid", "find", "search") a = Array("=", ">", "<", ">=", "<=", "<>", "BETWEEN") b = Array(xlEqual, xlGreater, xlLess, xlGreaterEqual, xlLessEqual, xlNotEqual, xlBetween) i = 1 Do While i <= c.FormatConditions.Count And Not témoin If c.FormatConditions(i).Type = xlCellValue Then tmp1 = Evaluate(c.FormatConditions(i).Formula1) oper = a(Application.Match(c.FormatConditions(i).Operator, b, 0) - 1) If oper <> "BETWEEN" Then If Evaluate(c & oper & tmp1) Then coul = c.FormatConditions(i).Interior.ColorIndex témoin = True End If Else tmp2 = Evaluate(c.FormatConditions(i).Formula2) If Evaluate("AND(" & c & ">=" & tmp1 & "," & c & "<=" & tmp2 & ")") Then coul = c.FormatConditions(i).Interior.ColorIndex témoin = True End If End If Else z = c.FormatConditions(i).Formula1 For k = LBound(ff) To UBound(ff) z = Replace(z, UCase(ff(k)), UCase(fa(k))) Next k If Evaluate(z) = True Then coul = c.FormatConditions(i).Interior.ColorIndex témoin = True End If End If i = i + 1 Loop CouleurMFC = coul End Function
JB
On 25 jan, 17:07, magic-dd wrote:
salut
j'ai bien testé la combinaison qui permet de recuperer lacouleuret la valeur d'une cellule
or
est il possible de recuperer la valeur et lacouleurissue d'une mise en forme conditionelle