Conditional Formatting VBA

[Guest]

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

 

[Guest]

Just include FormatConditions in the code string. 4x

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

 

[Jim Cone]

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" <[email protected]>
wrote in message
Is there a way to get the color of a cell (like with colorindex) but when
conditional formatting is used?

 

[Guest]

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?

 

[Guest]

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?

 

[Jim Cone]

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" <[email protected]>
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?

 

[Roger Govier]

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

 

[Guest]

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...

 

[Guest]

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]

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-
([email protected])



"GSB" <[email protected]>
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.

 

[Guest]

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?

 

[Roger Govier]

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.

 

[Guest]

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