Format numbers within a string with colour

[LGMan]

Hi, currently I have a data table from which I use a lookup formaulas to list data ranked in highest order Showing the team reference and its percentage result with a text string such as XYX123(99.56%), XYZ456(95.23)
The formula that I've used is...
=VLOOKUP(1,A610,2,0)&"("Text(vlookup(1,A610,4,0),"0.00")&"%"),"&VLOOKUP(2,A610,2,0)&"("Text(vlookup(1,A610,4,0),"0.00")&"%")

Currently I copy the text string into an email with an image of the table, but use color formatting to show the team reference and percentage in green if greater than 96%, and red if less that 96%.

Is there anyway to achieve this manual step within Excel with Conditional Formats or another approach?

Mnay Thanks

 

[SkipVought]

Hi,

Well did you try Conditional Formatting?

If so, what were your results and exactly what part of CF did you use and how?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[LGMan]

Hi I couldn't get any CFs to work , rather I don't really know what I could put in the CF to cover the whole string. Perhaps VBA might be better, or should I spilt the formula up to seperate cells rather than having it all in one cell?

 

[SkipVought]

Exactly what did you do that did not work? CF should do it!


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[LGMan]

CFs aren't my strong point, I tried the format values greater or equal to .99 to be bold green, but that makes the whole tring bold green, rather than just the 99.56

 

[SkipVought]

Format WHAT values? What was you CF criteria FORMULA?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Format numbers within a string with colour"

"Currently I copy the text string...use color formatting to show the team reference and percentage in green if greater than 96%, and red if less that 96%.

These seem to indicate two different objectives.

Please note that your formula returns ONE SINGLE VALUE, in this case a string. There is no way to parse the results of a formula. It is considered ONE VALUE.

Now if you want to CF the entire value, which the second quote seems to indicate, then you must use the value, which you get from this formula: vlookup(1,A610,4,0) (which I fail to understand as it ALWAYS returns the value from the 4th column for the value of 1 in A6:A10??? ALWAYS!) So that value will ALWAYS be the same? I DON'T GET IT! But THATS the formula/value you need to test in your CF Formula.

Please post your A510 lookup table.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[LGMan]

Hi Skip, the table is B610 where the Team References are in B6:B10 and their results are in D610. Within A6:A10 I have a rank set against the results in high to low order.
I'd like to have something like this

 Example of required format
XYZ123[green](99.56)[/green], XYZ456 [red](95.23)[/red], XYZ651[red](95.11)[/red], XYZ874[red](94.88)[/red] & XYZ874[red](91.55)[/red]

 

[SkipVought]

Please! I asked you to post your LOOKUP TABLE that you reference in your formula:
[tt]
=VLOOKUP(1,A610,2,0)&"("Text(vlookup(1,A610,4,0),"0.00")&"%"),"&VLOOKUP(2,A610,2,0)&"("Text(vlookup(1,A610,4,0),"0.00")&"%")
[/tt]


THAT, what you want to do, cannot be accomplished by ANY means, CF or VBA, when your results are returned by [highlight #FCE94F]a formula[/highlight][highlight #FCE94F][/highlight].

You will notice that in the Formula Bar, you have a FORMULA. In the cell you have the results of the formula.

I'd suggest, rather than using a formula, you use VBA to return a STRING to the cell, containing the formatting required.

Please post in forum707.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kendue]

Make the entire column showing percentages green to start with, and use conditional formula to change percentages less than 96.00 = "red".