Hi Rick
Yes; it is working and I really appreciate the enthusiasm and time behind
this. However, dont you think it looks a bit complicated.
If you go by how Microsoft has designed the 'Conditional Formatting' user
interface and apply the same sequence while coding I think it is quite
straightforward. The below can be used as a UDF for 2003 version. Having said
that I am not sure whether this would work for 2007.
Function GetCFColorIndex(C As Range) As Variant
Dim intCount As Integer, FC As FormatCondition, blnMatch As Boolean
If C.Count < 1 Then Exit Function
For intCount = 1 To C.FormatConditions.Count
'Loop through each Contidional Formatting
Set FC = C.FormatConditions(intCount)
If FC.Type = 1 Then
'Handle Type1-xlExpression (If 'Cell Value Is')
Select Case FC.Operator
Case xlBetween '1
If C.Value = FC.Formula1 And C.Value <= FC.Formula2 _
Then blnMatch = True: Exit For
Case xlNotBetween '2
If C.Value < FC.Formula1 Or C.Value FC.Formula2 Then _
blnMatch = True: Exit For
Case xlEqual '3
If C.Value = FC.Formula1 Then blnMatch = True: Exit For
Case xlNotEqual '4
If C.Value < FC.Formula1 Then blnMatch = True: Exit For
Case xlGreater '5
If C.Value FC.Formula1 Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If C.Value = FC.Formula1 Then blnMatch = True: Exit For
Case xlLess '7
If C.Value < FC.Formula1 Then blnMatch = True: Exit For
Case xlLessEqual '8
If C.Value <= FC.Formula1 Then blnMatch = True: Exit For
End Select
Else
'Handle Type2-xlExternal (If 'Formula Is')
If Evaluate(FC.Formula1) Then blnMatch = True: Exit For
End If
Next
If blnMatch Then GetCFColorIndex = FC.Interior.ColorIndex
End Function
If this post helps click Yes
---------------
Jacob Skaria
"Rick Rothstein" wrote:
Ah, I see the problem now. Okay, what about the following function then? I
think I caught all the problem areas, but I can't be sure... the Conditional
Format structure seems like such a complicated mess underneath it all.
Function GetCellColorIndex(C As Range) As Variant
Dim X As Long, Op As Long, Condition As Boolean, FC As FormatCondition
Dim CurrAddr As String, CVal As Variant, Operators() As String
Operators = Split("=,<,=,<,,<,=,<=,<=,", ",")
If C.Count = 1 Then
CurrAddr = ActiveCell.Address
C.Select
For X = 1 To C.FormatConditions.Count
Set FC = C.FormatConditions(X)
If FC.Type = xlExpression Then
If Evaluate(FC.Formula1) Then GoTo Done
Else
If IsEmpty(C.Value) Then
CVal = """"""
Else
CVal = C.Value
End If
Op = FC.Operator
If Op = xlBetween Then
If Evaluate(CVal & Operators(Op - 1) & FC.Formula1) And _
Evaluate(CVal & Operators(Op + 7) & FC.Formula2) _
Then GoTo Done
ElseIf Op = xlNotBetween Then
If Evaluate(CVal & Operators(Op - 1) & FC.Formula1) Or _
Evaluate(CVal & Operators(Op + 7) & FC.Formula2) _
Then GoTo Done
ElseIf Left(FC.Formula1, 1) = "=" Then
If Evaluate(CVal & Operators(Op - 1) & Mid(FC.Formula1, 2)) Then
GoTo Done
ElseIf Evaluate(CVal & Operators(Op - 1) & FC.Formula1) Then
GoTo Done
End If
End If
Next
GetCellColorIndex = C.Interior.ColorIndex
Else
GetCellColorIndex = CVErr(xlErrRef)
End If
GoTo RestoreActiveCell
Done:
GetCellColorIndex = FC.Interior.ColorIndex
RestoreActiveCell:
Range(CurrAddr).Select
End Function
I should also note, as written, this function is only good for active
worksheet... if you think it works properly (or nearly so<g), then I'll try
to modify it for non-active worksheets.
--
Rick (MVP - Excel)
"Jacob Skaria" wrote in message
...
Rick, I have tested this now/before and it is true that it returns the
color
index. Maybe you have missed the second post done by the OP.
"Those expressions tell me what colorindex I have set for each condition
and
how many conditions I'm using, but they don't tell me which one is
active."
If you really need an example.. FormatCell Value IsEqual to2 and try
passing a cell reference which is blank..
If this post helps click Yes
---------------
Jacob Skaria
"Rick Rothstein" wrote:
Did you try my function? I'm pretty sure it will work correctly for all
Conditional Formats... give it a try. And if you find a condition that it
does not work for, please let me know and I will try to adjust for it.
--
Rick (MVP - Excel)
"Jacob Skaria" wrote in message
...
Rick, more conditions are to be dealt with like 'Cell Value' equal to,
not
equal to, greater than and so on ..to make this work for any CF
conditions;;
"Rick Rothstein" wrote:
One last point about this function... it can **only** be used from
within
your own VB code... it **cannot** be used as a UDF (User Defined
Function).
--
Rick (MVP - Excel)
"Rick Rothstein" wrote in
message
...
Here is a slight modification that will keep the currently active
cell
active after the function has finished running...
Function GetCellColorIndex(C As Range) As Variant
Dim Op As Long, Condition As Boolean, CurrAddr As String
If C.Count = 1 Then
CurrAddr = ActiveCell.Address
C.Select
On Error Resume Next
For X = 1 To C.FormatConditions.Count
Op = C.FormatConditions(X).Operator
If Evaluate(C.FormatConditions(X).Formula1) Then
If Op = xlBetween Or Op = xlNotBetween Then
Condition = Evaluate(C.FormatConditions(X).Formula2)
Else
Condition = True
End If
If Condition Then
GetCellColorIndex =
C.FormatConditions(X).Interior.ColorIndex
Range(CurrAddr).Select
Exit Function
End If
End If
Next
GetCellColorIndex = C.Interior.ColorIndex
Else
GetCellColorIndex = CVErr(xlErrRef)
End If
Range(CurrAddr).Select
End Function
--
Rick (MVP - Excel)
"Rick Rothstein" wrote in
message
...
The following function will return the ColorIndex of the cell
passed
into
it. If a Conditional Format is in effect, the function will return
the
ColorIndex imposed by the Conditional Format; otherwise, it will
return
the cell's interior ColorIndex. If you pass more than one cell to
the
function, it will error out (you can test this if need be by using
the
IsError function).
Function GetCellColorIndex(C As Range) As Variant
Dim Op As Long, Condition As Boolean
If C.Count = 1 Then
C.Select
On Error Resume Next
For X = 1 To Range("B3").FormatConditions.Count
Op = C.FormatConditions(X).Operator
If Evaluate(C.FormatConditions(X).Formula1) Then
If Op = xlBetween Or Op = xlNotBetween Then
Condition = Evaluate(C.FormatConditions(X).Formula2)
Else
Condition = True
End If
If Condition Then
GetCellColorIndex =
C.FormatConditions(X).Interior.ColorIndex
Exit Function
End If
End If
Next
GetCellColorIndex = C.Interior.ColorIndex
Else
GetCellColorIndex = CVErr(xlErrRef)
End If
End Function
--
Rick (MVP - Excel)
"Al_82" wrote in message
...
Is there any way to determine the colorindex of a conditionally
formatted cell?
--
Al_82