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

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