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