AW: Farbwert der bedingten Formatierung abfragen
10.11.2005 08:49:40
Alexander
Ich lass mich nur ungern lumpen, also hier die Lösung:
Sub test()
Dim sh As Worksheet
Set sh = ActiveSheet
With sh.Range("A1")
For i = 1 To .FormatConditions.Count
.Offset(i, 0).Interior.ColorIndex = .FormatConditions(i).Interior.ColorIndex
If .FormatConditions(i).Operator = xlBetween Or .FormatConditions(i).Operator = xlNotBetween Then
If testCondition(.Value, .FormatConditions(i).Operator, .FormatConditions(i).Formula1, .FormatConditions(i).Formula2) Then
.Offset(i, 0).Value = "X"
Else
.Offset(i, 0).Value = ""
End If
Else
If testCondition(.Value, .FormatConditions(i).Operator, .FormatConditions(i).Formula1) Then
.Offset(i, 0).Value = "X"
Else
.Offset(i, 0).Value = ""
End If
End If
Next i
End With
End Sub
Function testCondition(Value As String, Operator As Long, F1 As String, Optional F2 As String) As Boolean
Select Case Operator
Case xlBetween
If (Value >= F1 And Value <= F2) Or (Value >= F2 And Value <= F1) Then testCondition = True
Case xlEqual
If Value = F1 Then testCondition = True
Case xlGreater
If Value > F1 Then testCondition = True
Case xlGreaterEqual
If Value >= F1 Then testCondition = True
Case xlLess
If Value < F1 Then testCondition = True
Case xlLessEqual
If Value <= F1 Then testCondition = True
Case xlNotBetween
If Not ((Value >= F1 And Value <= F2) Or (Value >= F2 And Value <= F1)) Then testCondition = True
Case xlNotEqual
If Value <> F1 Then testCondition = True
End Select
End Function
Man kann test natürlich beenden, wenn die erste Bedingung zutrifft, weil Excel das ja auch so macht.
Gruß, Alexander