Conditional Formatting VBA
 

Is there a way to get the color of a cell (like with colorindex) but when
conditional formatting is used?

Conditional Formatting VBA
 

Just include FormatConditions in the code string. 4x

Range("A1").FormatCoditions.Interior.ColorIndex = 3

"GSB" wrote:

Is there a way to get the color of a cell (like with colorindex) but when
conditional formatting is used?

Conditional Formatting VBA
 

Chip Pearson has code to do that but it is not for the faint of heart...
http://www.cpearson.com/excel/CFColors.htm

I have a free Excel add-in - "Determine Colors", available upon request,
that lists cell colors (including CF) on the cell right-click popup menu.

It may be of help to color-blind Excel users.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(remove xxx from my email address)


"GSB"
wrote in message
Is there a way to get the color of a cell (like with colorindex) but when
conditional formatting is used?

Conditional Formatting VBA
 

Thanx Jim, i already saw CPearson´s code... but first of all, i wonder if it
works with excel 2007 cause it has a lot more CF options than before. Also,
do you think there is an easier way to get the CF Color that is applied to
certain cell?

"Jim Cone" wrote:



Chip Pearson has code to do that but it is not for the faint of heart...
http://www.cpearson.com/excel/CFColors.htm

I have a free Excel add-in - "Determine Colors", available upon request,
that lists cell colors (including CF) on the cell right-click popup menu.

It may be of help to color-blind Excel users.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(remove xxx from my email address)


"GSB"
wrote in message
Is there a way to get the color of a cell (like with colorindex) but when
conditional formatting is used?

Conditional Formatting VBA
 

Thx for your answer. Maybe i wasnt clear before, but Im actually trying to
get the CF Color of a selected cell to use it in a graph, I dont want to
change it. Any ideas?

"JLGWhiz" wrote:

Just include FormatConditions in the code string. 4x

Range("A1").FormatCoditions.Interior.ColorIndex = 3

"GSB" wrote:

Is there a way to get the color of a cell (like with colorindex) but when
conditional formatting is used?

Conditional Formatting VBA
 

I don't have XL 2007.
Why don't you try the Chip Pearson code and see how it works for you?
I pretty much rolled my own code for the Determine Colors program,
but it certainly is not any less complicated.
I am not aware of any "easy" method.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins: special sorting, comparing, matching, listing, finding...)



"GSB"
wrote in message
Thanx Jim, i already saw CPearson´s code... but first of all, i wonder if it
works with excel 2007 cause it has a lot more CF options than before. Also,
do you think there is an easier way to get the CF Color that is applied to
certain cell?



"Jim Cone" wrote:
Chip Pearson has code to do that but it is not for the faint of heart...
http://www.cpearson.com/excel/CFColors.htm
I have a free Excel add-in - "Determine Colors", available upon request,
that lists cell colors (including CF) on the cell right-click popup menu.
It may be of help to color-blind Excel users.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(remove xxx from my email address)




"GSB"
wrote in message
Is there a way to get the color of a cell (like with colorindex) but when
conditional formatting is used?

Conditional Formatting VBA
 

Hi

Depending upon what type of formatting you have applied in XL2007, try
something like the following which works for Data Bars

Sub test()
With Selection.FormatConditions(1).BarColor
Debug.Print .Color
Debug.Print .TintAndShade
End With
End Sub

--
Regards

Roger Govier


"GSB" wrote in message
...
Thanx Jim, i already saw CPearson´s code... but first of all, i wonder
if it
works with excel 2007 cause it has a lot more CF options than before.
Also,
do you think there is an easier way to get the CF Color that is
applied to
certain cell?

"Jim Cone" wrote:



Chip Pearson has code to do that but it is not for the faint of
heart...
http://www.cpearson.com/excel/CFColors.htm

I have a free Excel add-in - "Determine Colors", available upon
request,
that lists cell colors (including CF) on the cell right-click popup
menu.

It may be of help to color-blind Excel users.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(remove xxx from my email address)


"GSB"
wrote in message
Is there a way to get the color of a cell (like with colorindex) but
when
conditional formatting is used?

Conditional Formatting VBA
 

Roger:
I tried your code but couldnt see any result, what is it supposed to happen?
I think something should be displayed somewhere but i dont see anything.

The type of formatting im using is to color each cell dependig on the text
it contains, for example, if it says friday, it turns blue, if it says
monday, it turns red.. and so on...



"Roger Govier" wrote:

Hi

Depending upon what type of formatting you have applied in XL2007, try
something like the following which works for Data Bars

Sub test()
With Selection.FormatConditions(1).BarColor
Debug.Print .Color
Debug.Print .TintAndShade
End With
End Sub

--
Regards

Roger Govier


"GSB" wrote in message
...
Thanx Jim, i already saw CPearson´s code... but first of all, i wonder
if it
works with excel 2007 cause it has a lot more CF options than before.
Also,
do you think there is an easier way to get the CF Color that is
applied to
certain cell?

"Jim Cone" wrote:



Chip Pearson has code to do that but it is not for the faint of
heart...
http://www.cpearson.com/excel/CFColors.htm

I have a free Excel add-in - "Determine Colors", available upon
request,
that lists cell colors (including CF) on the cell right-click popup
menu.

It may be of help to color-blind Excel users.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(remove xxx from my email address)


"GSB"
wrote in message
Is there a way to get the color of a cell (like with colorindex) but
when
conditional formatting is used?

Conditional Formatting VBA
 

Jim: I tried C. Pearson´s code and didnt work. The ActiveCondition functions
turns "0" everytime. It seems like it doesn´t detect the FC.
I tried adding this to the code

Case xlContains
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(Rng.Value) = CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If Rng.Value = Temp Then
ActiveCondition = Ndx
Exit Function
End If
End If

But it didn´t work.



"Jim Cone" wrote:


I don't have XL 2007.
Why don't you try the Chip Pearson code and see how it works for you?
I pretty much rolled my own code for the Determine Colors program,
but it certainly is not any less complicated.
I am not aware of any "easy" method.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins: special sorting, comparing, matching, listing, finding...)



"GSB"
wrote in message
Thanx Jim, i already saw CPearson´s code... but first of all, i wonder if it
works with excel 2007 cause it has a lot more CF options than before. Also,
do you think there is an easier way to get the CF Color that is applied to
certain cell?



"Jim Cone" wrote:
Chip Pearson has code to do that but it is not for the faint of heart...
http://www.cpearson.com/excel/CFColors.htm
I have a free Excel add-in - "Determine Colors", available upon request,
that lists cell colors (including CF) on the cell right-click popup menu.
It may be of help to color-blind Excel users.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(remove xxx from my email address)




"GSB"
wrote in message
Is there a way to get the color of a cell (like with colorindex) but when
conditional formatting is used?

Conditional Formatting VBA
 

Again, I don't have XL 2007.
It would be interesting, to me at least, to determine if my free add-in
"Determine Colors" will install on that version and accurately return CF colors.
I have no idea what MS has done or undone to the color system in 07.
Trying it might give you some clue as to whether new code is required to
return CF colors in the 07 Excel version.

The program displays the name of the color for the cell interior, pattern, border, font
and for the sheet tab on a popup menu on the cell right-click menu.
The color name reflects the color displayed however derived.

Anyone interested can request it by email. Please include your name and what
part of the world you are in with your request.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
-remove XXX from the address below-
)



"GSB"
wrote in message
Jim:
I tried C. Pearson´s code and didnt work. The ActiveCondition functions
turns "0" everytime. It seems like it doesn´t detect the FC.
I tried adding this to the code

Case xlContains
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(Rng.Value) = CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If Rng.Value = Temp Then
ActiveCondition = Ndx
Exit Function
End If
End If

But it didn´t work.

Conditional Formatting VBA
 

Well, now i am confussed, i tried

=CFColorindex(A7)

in Excel 2003 and it gave me a Value error...
My cell originally had a "Cell Value Is" Condition and after I wun the Macro
it turned into a "Formula Is" Condition... is this normal?



"Jim Cone" wrote:


I don't have XL 2007.
Why don't you try the Chip Pearson code and see how it works for you?
I pretty much rolled my own code for the Determine Colors program,
but it certainly is not any less complicated.
I am not aware of any "easy" method.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins: special sorting, comparing, matching, listing, finding...)



"GSB"
wrote in message
Thanx Jim, i already saw CPearson´s code... but first of all, i wonder if it
works with excel 2007 cause it has a lot more CF options than before. Also,
do you think there is an easier way to get the CF Color that is applied to
certain cell?



"Jim Cone" wrote:
Chip Pearson has code to do that but it is not for the faint of heart...
http://www.cpearson.com/excel/CFColors.htm
I have a free Excel add-in - "Determine Colors", available upon request,
that lists cell colors (including CF) on the cell right-click popup menu.
It may be of help to color-blind Excel users.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(remove xxx from my email address)




"GSB"
wrote in message
Is there a way to get the color of a cell (like with colorindex) but when
conditional formatting is used?

Conditional Formatting VBA
 

Hi

You should see the colour number and Tint and shade value displayed in
the immediate window.
Use Control + G to bring up the immediate window before running the
code.

--
Regards

Roger Govier


"GSB" wrote in message
...
Roger:
I tried your code but couldnt see any result, what is it supposed to
happen?
I think something should be displayed somewhere but i dont see
anything.

The type of formatting im using is to color each cell dependig on the
text
it contains, for example, if it says friday, it turns blue, if it says
monday, it turns red.. and so on...



"Roger Govier" wrote:

Hi

Depending upon what type of formatting you have applied in XL2007,
try
something like the following which works for Data Bars

Sub test()
With Selection.FormatConditions(1).BarColor
Debug.Print .Color
Debug.Print .TintAndShade
End With
End Sub

--
Regards

Roger Govier


"GSB" wrote in message
...
Thanx Jim, i already saw CPearson´s code... but first of all, i
wonder
if it
works with excel 2007 cause it has a lot more CF options than
before.
Also,
do you think there is an easier way to get the CF Color that is
applied to
certain cell?

"Jim Cone" wrote:



Chip Pearson has code to do that but it is not for the faint of
heart...
http://www.cpearson.com/excel/CFColors.htm

I have a free Excel add-in - "Determine Colors", available upon
request,
that lists cell colors (including CF) on the cell right-click
popup
menu.

It may be of help to color-blind Excel users.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(remove xxx from my email address)


"GSB"
wrote in message
Is there a way to get the color of a cell (like with colorindex)
but
when
conditional formatting is used?

Conditional Formatting VBA
 

Jim, i just downloaded your add in and it doesnt work with conditional format
in XL 2007, normal format works fine.

"Jim Cone" wrote:


Again, I don't have XL 2007.
It would be interesting, to me at least, to determine if my free add-in
"Determine Colors" will install on that version and accurately return CF colors.
I have no idea what MS has done or undone to the color system in 07.
Trying it might give you some clue as to whether new code is required to
return CF colors in the 07 Excel version.

The program displays the name of the color for the cell interior, pattern, border, font
and for the sheet tab on a popup menu on the cell right-click menu.
The color name reflects the color displayed however derived.

Anyone interested can request it by email. Please include your name and what
part of the world you are in with your request.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
-remove XXX from the address below-
)



"GSB"
wrote in message
Jim:
I tried C. Pearson´s code and didnt work. The ActiveCondition functions
turns "0" everytime. It seems like it doesn´t detect the FC.
I tried adding this to the code

Case xlContains
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(Rng.Value) = CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If Rng.Value = Temp Then
ActiveCondition = Ndx
Exit Function
End If
End If

But it didn´t work.