Is there any way to determine the colorindex of a conditionally formatted cell?
--
Al_82

Activecell.FormatConditions(1).Interior.ColorIndex
to check the condition 1 colorindex

Activecell.FormatConditions(2).Interior.ColorIndex
to check the condition 2 colorindex

Activecell.FormatConditions.Count
to get the number of conditions applied

If this post helps click Yes
---------------
Jacob Skaria


"Al_82" wrote:

Is there any way to determine the colorindex of a conditionally formatted cell?
--
Al_82

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.
That's what I'm looking for.
--
Al_82

No direct way to get that. You will have to loop through the number of
conditional format conditions applied to that cell and validate the cell
value with the conditions applied to find which condition is satisfied or to
see whether any of the conditions are satisfied..

For more info refer the below link by Chip Pearson
http://www.cpearson.com/excel/CFColors.htm

If this post helps click Yes
---------------
Jacob Skaria


"Al_82" wrote:

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.
That's what I'm looking for.
--
Al_82

Thanks Jacob. That's what I was afraid the answer would be. I already
looked through Chip's link you posted and I didn't see any reference to
colors of conditionally formatted cells, so that's why my question. In my
application I'm looking to use an "executive summary" row as an indicator of
conditional formatting results in a worksheet that's impratically large for a
user to scan looking for a particular condition. I'll just use a hidden
helper worksheet to extract what I need.

Thanks again.
--
Al_82


"Jacob Skaria" wrote:

No direct way to get that. You will have to loop through the number of
conditional format conditions applied to that cell and validate the cell
value with the conditions applied to find which condition is satisfied or to
see whether any of the conditions are satisfied..

For more info refer the below link by Chip Pearson
http://www.cpearson.com/excel/CFColors.htm

If this post helps click Yes
---------------
Jacob Skaria


"Al_82" wrote:

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.
That's what I'm looking for.
--
Al_82

To your response

"Chip's link you posted and I didn't see any reference to colors of conditionally formatted cells"

The page content starts of saying the below ..Am I missing something here..

"Unfortunately, the Color and ColorIndex properties of a Range don't return
the color of a cell that is displayed if Conditional formatting is applied to
the cell. Nor does it allow you to determine whether a conditional format is
currently in effect for a cell. "

If this post helps click Yes
---------------
Jacob Skaria


"Al_82" wrote:

Thanks Jacob. That's what I was afraid the answer would be. I already
looked through Chip's link you posted and I didn't see any reference to
colors of conditionally formatted cells, so that's why my question. In my
application I'm looking to use an "executive summary" row as an indicator of
conditional formatting results in a worksheet that's impratically large for a
user to scan looking for a particular condition. I'll just use a hidden
helper worksheet to extract what I need.

Thanks again.
--
Al_82


"Jacob Skaria" wrote:

No direct way to get that. You will have to loop through the number of
conditional format conditions applied to that cell and validate the cell
value with the conditions applied to find which condition is satisfied or to
see whether any of the conditions are satisfied..

For more info refer the below link by Chip Pearson
http://www.cpearson.com/excel/CFColors.htm

If this post helps click Yes
---------------
Jacob Skaria


"Al_82" wrote:

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.
That's what I'm looking for.
--
Al_82

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

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

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

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

As I said, I didn't see that. Thanks for pointing it out.
--
Al_82


"Jacob Skaria" wrote:

To your response

"Chip's link you posted and I didn't see any reference to colors of conditionally formatted cells"

The page content starts of saying the below ..Am I missing something here..

"Unfortunately, the Color and ColorIndex properties of a Range don't return
the color of a cell that is displayed if Conditional formatting is applied to
the cell. Nor does it allow you to determine whether a conditional format is
currently in effect for a cell. "

If this post helps click Yes
---------------
Jacob Skaria


"Al_82" wrote:

Thanks Jacob. That's what I was afraid the answer would be. I already
looked through Chip's link you posted and I didn't see any reference to
colors of conditionally formatted cells, so that's why my question. In my
application I'm looking to use an "executive summary" row as an indicator of
conditional formatting results in a worksheet that's impratically large for a
user to scan looking for a particular condition. I'll just use a hidden
helper worksheet to extract what I need.

Thanks again.
--
Al_82


"Jacob Skaria" wrote:

No direct way to get that. You will have to loop through the number of
conditional format conditions applied to that cell and validate the cell
value with the conditions applied to find which condition is satisfied or to
see whether any of the conditions are satisfied..

For more info refer the below link by Chip Pearson
http://www.cpearson.com/excel/CFColors.htm

If this post helps click Yes
---------------
Jacob Skaria


"Al_82" wrote:

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.
That's what I'm looking for.
--
Al_82

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

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

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

I see one of the lines word wrapped in a bad location which might throw some
people, so here is the function with a line continuation inserted so that
the line won't wrap in that bad spot...

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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

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

Rick; both of us have missed to handle text strings..

"Jacob Skaria" wrote:

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

I thought mine did... can you give me an example so I can hone in on the
problem?

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Rick; both of us have missed to handle text strings..

"Jacob Skaria" wrote:

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

Rick, Just try any text string under the first type 'Cell Value' is "A". I
have modified mine which passed the initial testing. It is time to have a
look at XL07..


'UDF to get Conditional Formatting Color Index for a cell (XL 2003)
'-------------------------------------------------------------------------------
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 = GetCFV(FC.Formula1) And C.Value _
<= GetCFV(FC.Formula2) Then blnMatch = True: Exit For
Case xlNotBetween '2
If C.Value < GetCFV(FC.Formula1) Or C.Value _
GetCFV(FC.Formula2) Then blnMatch = True: Exit For
Case xlEqual '3
If C.Value = GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlNotEqual '4
If C.Value < GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreater '5
If C.Value GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If C.Value = GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLess '7
If C.Value < GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLessEqual '8
If C.Value <= GetCFV(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
'-------------------------------------------------------------------------------
Function GetCFV(strData As Variant)
'Get text string or numeric from CF formula
GetCFV = strData
If Not IsNumeric(strData) Then _
GetCFV = Mid(strData, 3, Len(strData) - 3)
End Function
'-------------------------------------------------------------------------------

If this post helps click Yes
---------------
Jacob Skaria


"Rick Rothstein" wrote:

I thought mine did... can you give me an example so I can hone in on the
problem?

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Rick; both of us have missed to handle text strings..

"Jacob Skaria" wrote:

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

Sorry, but I get both your old and new code missing some conditions (with
your new code missing more... fixing some of the ones the old code missed
plus adding new misses to the batch). Here are the set-ups that I get your
code failing with...

Old Code
=================
Cell value is - not between 10 and 14 == Cell content = 1201
Cell value is - equal to ="" == Cell content is empty
Formula is - =SUM(A1,A3)=3 == Cell contents A1=2, A3=1

New Code
=================
Cell value is - equal to 2 == Cell content = 2
Cell value is - equal to ="Rick" == Cell content = Rick
Cell value is - greater than 0 == Cell content = 1
Cell value is - greater than or equal to 12 == Cell content = 12
Cell value is - between 10 and 14 == Cell content = 12
Formula is - =SUM(A1,A3)=3 == Cell contents A1=2, A3=1

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Rick, Just try any text string under the first type 'Cell Value' is "A". I
have modified mine which passed the initial testing. It is time to have a
look at XL07..


'UDF to get Conditional Formatting Color Index for a cell (XL 2003)
'-------------------------------------------------------------------------------
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 = GetCFV(FC.Formula1) And C.Value _
<= GetCFV(FC.Formula2) Then blnMatch = True: Exit For
Case xlNotBetween '2
If C.Value < GetCFV(FC.Formula1) Or C.Value _
GetCFV(FC.Formula2) Then blnMatch = True: Exit For
Case xlEqual '3
If C.Value = GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlNotEqual '4
If C.Value < GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreater '5
If C.Value GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If C.Value = GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLess '7
If C.Value < GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLessEqual '8
If C.Value <= GetCFV(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
'-------------------------------------------------------------------------------
Function GetCFV(strData As Variant)
'Get text string or numeric from CF formula
GetCFV = strData
If Not IsNumeric(strData) Then _
GetCFV = Mid(strData, 3, Len(strData) - 3)
End Function
'-------------------------------------------------------------------------------

If this post helps click Yes
---------------
Jacob Skaria


"Rick Rothstein" wrote:

I thought mine did... can you give me an example so I can hone in on the
problem?

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Rick; both of us have missed to handle text strings..

"Jacob Skaria" wrote:

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

Rick, Jacob,

Apologies for breaking in on your thread but I think Bob Philips has already
done this


Bob's function or view his page

http://www.xldynamic.com/source/xld.CFConditions.html

'---------------------------------------------------------------------
Public Function CFColorindex(rng As Range)
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

Set rng = rng(1, 1)
If rng.FormatConditions.Count 0 Then
For Each oFC In rng.FormatConditions
If oFC.Type = xlCellValue Then
Select Case oFC.Operator
Case xlEqual
CFColorindex = rng.Value = oFC.Formula1
Case xlNotEqual
CFColorindex = rng.Value < oFC.Formula1
Case xlGreater
CFColorindex = rng.Value oFC.Formula1
Case xlGreaterEqual
CFColorindex = rng.Value = oFC.Formula1
Case xlLess
CFColorindex = rng.Value < oFC.Formula1
Case xlLessEqual
CFColorindex = rng.Value <= oFC.Formula1
Case xlBetween
CFColorindex = (rng.Value = oFC.Formula1 And _
rng.Value <= oFC.Formula2)
Case xlNotBetween
CFColorindex = (rng.Value < oFC.Formula1 Or _
rng.Value oFC.Formula2)
End Select
Else
're-adjust the formula back to the formula that applies
'to the cell as relative formulae adjust to the activecell
With Application
iRow = rng.Row
iColumn = rng.Column
sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
sF1 = .Substitute(sF1, "COLUMN()", iColumn)
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
End With
CFColorindex = rng.Parent.Evaluate(sF1)
End If

If CFColorindex Then
If Not IsNull(oFC.Interior.ColorIndex) Then
CFColorindex = oFC.Interior.ColorIndex
Exit Function
End If
End If
Next oFC
End If 'rng.FormatConditions.Count 0

End Function

Mike


"Rick Rothstein" wrote:

Sorry, but I get both your old and new code missing some conditions (with
your new code missing more... fixing some of the ones the old code missed
plus adding new misses to the batch). Here are the set-ups that I get your
code failing with...

Old Code
=================
Cell value is - not between 10 and 14 == Cell content = 1201
Cell value is - equal to ="" == Cell content is empty
Formula is - =SUM(A1,A3)=3 == Cell contents A1=2, A3=1

New Code
=================
Cell value is - equal to 2 == Cell content = 2
Cell value is - equal to ="Rick" == Cell content = Rick
Cell value is - greater than 0 == Cell content = 1
Cell value is - greater than or equal to 12 == Cell content = 12
Cell value is - between 10 and 14 == Cell content = 12
Formula is - =SUM(A1,A3)=3 == Cell contents A1=2, A3=1

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Rick, Just try any text string under the first type 'Cell Value' is "A". I
have modified mine which passed the initial testing. It is time to have a
look at XL07..


'UDF to get Conditional Formatting Color Index for a cell (XL 2003)
'-------------------------------------------------------------------------------
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 = GetCFV(FC.Formula1) And C.Value _
<= GetCFV(FC.Formula2) Then blnMatch = True: Exit For
Case xlNotBetween '2
If C.Value < GetCFV(FC.Formula1) Or C.Value _
GetCFV(FC.Formula2) Then blnMatch = True: Exit For
Case xlEqual '3
If C.Value = GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlNotEqual '4
If C.Value < GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreater '5
If C.Value GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If C.Value = GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLess '7
If C.Value < GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLessEqual '8
If C.Value <= GetCFV(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
'-------------------------------------------------------------------------------
Function GetCFV(strData As Variant)
'Get text string or numeric from CF formula
GetCFV = strData
If Not IsNumeric(strData) Then _
GetCFV = Mid(strData, 3, Len(strData) - 3)
End Function
'-------------------------------------------------------------------------------

If this post helps click Yes
---------------
Jacob Skaria


"Rick Rothstein" wrote:

I thought mine did... can you give me an example so I can hone in on the
problem?

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Rick; both of us have missed to handle text strings..

"Jacob Skaria" wrote:

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

Hey! We were having fun here and you (and Bob) ruined it for us.<g

I misread something earlier in the thread which seemed to indicate that
Bob's function wasn't complete, but in testing it, I see that it is. Thanks
for the wake-up call.

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Rick, Jacob,

Apologies for breaking in on your thread but I think Bob Philips has
already
done this


Bob's function or view his page

http://www.xldynamic.com/source/xld.CFConditions.html

'---------------------------------------------------------------------
Public Function CFColorindex(rng As Range)
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

Set rng = rng(1, 1)
If rng.FormatConditions.Count 0 Then
For Each oFC In rng.FormatConditions
If oFC.Type = xlCellValue Then
Select Case oFC.Operator
Case xlEqual
CFColorindex = rng.Value = oFC.Formula1
Case xlNotEqual
CFColorindex = rng.Value < oFC.Formula1
Case xlGreater
CFColorindex = rng.Value oFC.Formula1
Case xlGreaterEqual
CFColorindex = rng.Value = oFC.Formula1
Case xlLess
CFColorindex = rng.Value < oFC.Formula1
Case xlLessEqual
CFColorindex = rng.Value <= oFC.Formula1
Case xlBetween
CFColorindex = (rng.Value = oFC.Formula1 And _
rng.Value <= oFC.Formula2)
Case xlNotBetween
CFColorindex = (rng.Value < oFC.Formula1 Or _
rng.Value oFC.Formula2)
End Select
Else
're-adjust the formula back to the formula that applies
'to the cell as relative formulae adjust to the activecell
With Application
iRow = rng.Row
iColumn = rng.Column
sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
sF1 = .Substitute(sF1, "COLUMN()", iColumn)
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
End With
CFColorindex = rng.Parent.Evaluate(sF1)
End If

If CFColorindex Then
If Not IsNull(oFC.Interior.ColorIndex) Then
CFColorindex = oFC.Interior.ColorIndex
Exit Function
End If
End If
Next oFC
End If 'rng.FormatConditions.Count 0

End Function

Mike


"Rick Rothstein" wrote:

Sorry, but I get both your old and new code missing some conditions (with
your new code missing more... fixing some of the ones the old code missed
plus adding new misses to the batch). Here are the set-ups that I get
your
code failing with...

Old Code
=================
Cell value is - not between 10 and 14 == Cell content = 1201
Cell value is - equal to ="" == Cell content is empty
Formula is - =SUM(A1,A3)=3 == Cell contents A1=2, A3=1

New Code
=================
Cell value is - equal to 2 == Cell content = 2
Cell value is - equal to ="Rick" == Cell content = Rick
Cell value is - greater than 0 == Cell content = 1
Cell value is - greater than or equal to 12 == Cell content = 12
Cell value is - between 10 and 14 == Cell content = 12
Formula is - =SUM(A1,A3)=3 == Cell contents A1=2, A3=1

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Rick, Just try any text string under the first type 'Cell Value' is
"A". I
have modified mine which passed the initial testing. It is time to have
a
look at XL07..


'UDF to get Conditional Formatting Color Index for a cell (XL 2003)
'-------------------------------------------------------------------------------
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 = GetCFV(FC.Formula1) And C.Value _
<= GetCFV(FC.Formula2) Then blnMatch = True: Exit For
Case xlNotBetween '2
If C.Value < GetCFV(FC.Formula1) Or C.Value _
GetCFV(FC.Formula2) Then blnMatch = True: Exit For
Case xlEqual '3
If C.Value = GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlNotEqual '4
If C.Value < GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreater '5
If C.Value GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If C.Value = GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLess '7
If C.Value < GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLessEqual '8
If C.Value <= GetCFV(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
'-------------------------------------------------------------------------------
Function GetCFV(strData As Variant)
'Get text string or numeric from CF formula
GetCFV = strData
If Not IsNumeric(strData) Then _
GetCFV = Mid(strData, 3, Len(strData) - 3)
End Function
'-------------------------------------------------------------------------------

If this post helps click Yes
---------------
Jacob Skaria


"Rick Rothstein" wrote:

I thought mine did... can you give me an example so I can hone in on
the
problem?

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in
message
...
Rick; both of us have missed to handle text strings..

"Jacob Skaria" wrote:

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

Rick,

Thanks for demonstrating that with VBA we should never say something can't
be done. While reading through your function, it occurred to me that I have
much easier ways to search my worksheet for the condition of interest then to
look for the format it sets. After all, the conditional format is intended
to get the users attention, not that of VBA.

Thank you for the lesson.
--
Al_82


"Rick Rothstein" wrote:

I see one of the lines word wrapped in a bad location which might throw some
people, so here is the function with a line continuation inserted so that
the line won't wrap in that bad spot...

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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

Mike; Thanks for that post. We are re-inventing the wheel...

Rick..thanks for testing that....it is getting better & refined

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 = GetCFV(FC.Formula1) And C.Value _
<= GetCFV(FC.Formula2) Then blnMatch = True: Exit For
Case xlNotBetween '2
If C.Value < GetCFV(FC.Formula1) Or C.Value _
GetCFV(FC.Formula2) Then blnMatch = True: Exit For
Case xlEqual '3
If C.Value = GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlNotEqual '4
If C.Value < GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreater '5
If C.Value GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If C.Value = GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLess '7
If C.Value < GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLessEqual '8
If C.Value <= GetCFV(FC.Formula1) Then blnMatch = True: Exit For
End Select
Else
'Handle Type2-xlExternal (If 'Formula Is')
If Evaluate(Application.ConvertFormula( _
Application.ConvertFormula(FC.Formula1, xlA1, xlR1C1), _
xlR1C1, xlA1, , C)) Then blnMatch = True: Exit For
End If
Next

If blnMatch Then GetCFColorIndex = FC.Interior.ColorIndex
End Function
'-------------------------------------------------------------------------------
Function GetCFV(strData As Variant)
'Get text string or numeric from CF formula
If Not IsNumeric(strData) Then
GetCFV = Mid(strData, 3, Len(strData) - 3)
Else
GetCFV = CDbl(strData)
End If
End Function
'-------------------------------------------------------------------------------



If this post helps click Yes
---------------
Jacob Skaria


"Rick Rothstein" wrote:

Hey! We were having fun here and you (and Bob) ruined it for us.<g

I misread something earlier in the thread which seemed to indicate that
Bob's function wasn't complete, but in testing it, I see that it is. Thanks
for the wake-up call.

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Rick, Jacob,

Apologies for breaking in on your thread but I think Bob Philips has
already
done this


Bob's function or view his page

http://www.xldynamic.com/source/xld.CFConditions.html

'---------------------------------------------------------------------
Public Function CFColorindex(rng As Range)
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

Set rng = rng(1, 1)
If rng.FormatConditions.Count 0 Then
For Each oFC In rng.FormatConditions
If oFC.Type = xlCellValue Then
Select Case oFC.Operator
Case xlEqual
CFColorindex = rng.Value = oFC.Formula1
Case xlNotEqual
CFColorindex = rng.Value < oFC.Formula1
Case xlGreater
CFColorindex = rng.Value oFC.Formula1
Case xlGreaterEqual
CFColorindex = rng.Value = oFC.Formula1
Case xlLess
CFColorindex = rng.Value < oFC.Formula1
Case xlLessEqual
CFColorindex = rng.Value <= oFC.Formula1
Case xlBetween
CFColorindex = (rng.Value = oFC.Formula1 And _
rng.Value <= oFC.Formula2)
Case xlNotBetween
CFColorindex = (rng.Value < oFC.Formula1 Or _
rng.Value oFC.Formula2)
End Select
Else
're-adjust the formula back to the formula that applies
'to the cell as relative formulae adjust to the activecell
With Application
iRow = rng.Row
iColumn = rng.Column
sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
sF1 = .Substitute(sF1, "COLUMN()", iColumn)
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
End With
CFColorindex = rng.Parent.Evaluate(sF1)
End If

If CFColorindex Then
If Not IsNull(oFC.Interior.ColorIndex) Then
CFColorindex = oFC.Interior.ColorIndex
Exit Function
End If
End If
Next oFC
End If 'rng.FormatConditions.Count 0

End Function

Mike


"Rick Rothstein" wrote:

Sorry, but I get both your old and new code missing some conditions (with
your new code missing more... fixing some of the ones the old code missed
plus adding new misses to the batch). Here are the set-ups that I get
your
code failing with...

Old Code
=================
Cell value is - not between 10 and 14 == Cell content = 1201
Cell value is - equal to ="" == Cell content is empty
Formula is - =SUM(A1,A3)=3 == Cell contents A1=2, A3=1

New Code
=================
Cell value is - equal to 2 == Cell content = 2
Cell value is - equal to ="Rick" == Cell content = Rick
Cell value is - greater than 0 == Cell content = 1
Cell value is - greater than or equal to 12 == Cell content = 12
Cell value is - between 10 and 14 == Cell content = 12
Formula is - =SUM(A1,A3)=3 == Cell contents A1=2, A3=1

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Rick, Just try any text string under the first type 'Cell Value' is
"A". I
have modified mine which passed the initial testing. It is time to have
a
look at XL07..


'UDF to get Conditional Formatting Color Index for a cell (XL 2003)
'-------------------------------------------------------------------------------
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 = GetCFV(FC.Formula1) And C.Value _
<= GetCFV(FC.Formula2) Then blnMatch = True: Exit For
Case xlNotBetween '2
If C.Value < GetCFV(FC.Formula1) Or C.Value _
GetCFV(FC.Formula2) Then blnMatch = True: Exit For
Case xlEqual '3
If C.Value = GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlNotEqual '4
If C.Value < GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreater '5
If C.Value GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If C.Value = GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLess '7
If C.Value < GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLessEqual '8
If C.Value <= GetCFV(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
'-------------------------------------------------------------------------------
Function GetCFV(strData As Variant)
'Get text string or numeric from CF formula
GetCFV = strData
If Not IsNumeric(strData) Then _
GetCFV = Mid(strData, 3, Len(strData) - 3)
End Function
'-------------------------------------------------------------------------------

If this post helps click Yes
---------------
Jacob Skaria


"Rick Rothstein" wrote:

I thought mine did... can you give me an example so I can hone in on
the
problem?

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in
message
...
Rick; both of us have missed to handle text strings..

"Jacob Skaria" wrote:

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

Mike and Bob,

Rick is right Bobs code do not handle text strings..

If this post helps click Yes
---------------
Jacob Skaria


"Rick Rothstein" wrote:

Hey! We were having fun here and you (and Bob) ruined it for us.<g

I misread something earlier in the thread which seemed to indicate that
Bob's function wasn't complete, but in testing it, I see that it is. Thanks
for the wake-up call.

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Rick, Jacob,

Apologies for breaking in on your thread but I think Bob Philips has
already
done this


Bob's function or view his page

http://www.xldynamic.com/source/xld.CFConditions.html

'---------------------------------------------------------------------
Public Function CFColorindex(rng As Range)
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

Set rng = rng(1, 1)
If rng.FormatConditions.Count 0 Then
For Each oFC In rng.FormatConditions
If oFC.Type = xlCellValue Then
Select Case oFC.Operator
Case xlEqual
CFColorindex = rng.Value = oFC.Formula1
Case xlNotEqual
CFColorindex = rng.Value < oFC.Formula1
Case xlGreater
CFColorindex = rng.Value oFC.Formula1
Case xlGreaterEqual
CFColorindex = rng.Value = oFC.Formula1
Case xlLess
CFColorindex = rng.Value < oFC.Formula1
Case xlLessEqual
CFColorindex = rng.Value <= oFC.Formula1
Case xlBetween
CFColorindex = (rng.Value = oFC.Formula1 And _
rng.Value <= oFC.Formula2)
Case xlNotBetween
CFColorindex = (rng.Value < oFC.Formula1 Or _
rng.Value oFC.Formula2)
End Select
Else
're-adjust the formula back to the formula that applies
'to the cell as relative formulae adjust to the activecell
With Application
iRow = rng.Row
iColumn = rng.Column
sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
sF1 = .Substitute(sF1, "COLUMN()", iColumn)
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
End With
CFColorindex = rng.Parent.Evaluate(sF1)
End If

If CFColorindex Then
If Not IsNull(oFC.Interior.ColorIndex) Then
CFColorindex = oFC.Interior.ColorIndex
Exit Function
End If
End If
Next oFC
End If 'rng.FormatConditions.Count 0

End Function

Mike


"Rick Rothstein" wrote:

Sorry, but I get both your old and new code missing some conditions (with
your new code missing more... fixing some of the ones the old code missed
plus adding new misses to the batch). Here are the set-ups that I get
your
code failing with...

Old Code
=================
Cell value is - not between 10 and 14 == Cell content = 1201
Cell value is - equal to ="" == Cell content is empty
Formula is - =SUM(A1,A3)=3 == Cell contents A1=2, A3=1

New Code
=================
Cell value is - equal to 2 == Cell content = 2
Cell value is - equal to ="Rick" == Cell content = Rick
Cell value is - greater than 0 == Cell content = 1
Cell value is - greater than or equal to 12 == Cell content = 12
Cell value is - between 10 and 14 == Cell content = 12
Formula is - =SUM(A1,A3)=3 == Cell contents A1=2, A3=1

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Rick, Just try any text string under the first type 'Cell Value' is
"A". I
have modified mine which passed the initial testing. It is time to have
a
look at XL07..


'UDF to get Conditional Formatting Color Index for a cell (XL 2003)
'-------------------------------------------------------------------------------
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 = GetCFV(FC.Formula1) And C.Value _
<= GetCFV(FC.Formula2) Then blnMatch = True: Exit For
Case xlNotBetween '2
If C.Value < GetCFV(FC.Formula1) Or C.Value _
GetCFV(FC.Formula2) Then blnMatch = True: Exit For
Case xlEqual '3
If C.Value = GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlNotEqual '4
If C.Value < GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreater '5
If C.Value GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If C.Value = GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLess '7
If C.Value < GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLessEqual '8
If C.Value <= GetCFV(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
'-------------------------------------------------------------------------------
Function GetCFV(strData As Variant)
'Get text string or numeric from CF formula
GetCFV = strData
If Not IsNumeric(strData) Then _
GetCFV = Mid(strData, 3, Len(strData) - 3)
End Function
'-------------------------------------------------------------------------------

If this post helps click Yes
---------------
Jacob Skaria


"Rick Rothstein" wrote:

I thought mine did... can you give me an example so I can hone in on
the
problem?

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in
message
...
Rick; both of us have missed to handle text strings..

"Jacob Skaria" wrote:

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