"Frank Kabel" wrote...
unfortunately there is nothing you can do. Colors created by a
conditional format can not be evaluated in VBA. That is if you check
the colorindex in VBA always the default color index is returned.

Wrong. While cells' color properties don't reflect colors set by conditional
formatting, those cells have FormatConditions collection object properties,
and those *CAN* be evaluated in VBA. The only trick is catching relative
cell refs in formula conditions, replacing refs to Application.Caller with
refs to cells in the proper range. This isn't fool-proof, but it handles
most likely situations.


Function ugh(rng As Range, ci As Variant) As Long
Dim f As FormatCondition, x As String, r As Range, a As String

a = Application.Caller.Address(0, 0)

For Each r In rng
For Each f In r.FormatConditions
If f.Interior.ColorIndex = ci Then

If f.Type = xlExpression Then
x = Application.WorksheetFunction.Substitute(f.Formula 1, _
a, r.Address(0, 0))
If Evaluate(x) Then ugh = ugh + 1

ElseIf (f.Operator = xlEqual And r.Value = f.Formula1) _
Or (f.Operator = xlNotEqual And r.Value < f.Formula1) _
Or (f.Operator = xlLess And r.Value < f.Formula1) _
Or (f.Operator = xlLessEqual And r.Value <= f.Formula1) _
Or (f.Operator = xlGreater And r.Value f.Formula1) _
Or (f.Operator = xlGreaterEqual And r.Value = f.Formula1) Then
ugh = ugh + 1

ElseIf (f.Operator = xlBetween And f.Formula1 <= r.Value _
And r.Value <= f.Formula2) Or (f.Operator = xlNotBetween _
And (r.Value < f.Formula1 Or f.Formula2 < r.Value)) Then
ugh = ugh + 1

End If

End If
Next f
Next r

End Function