Distinct colours in a cell and conditional formatting

If you wish to format on a char by char on an automated basis you will need to use VBA.

Let us know if that's a route you want to go down and if so, how the strings are populated - ie manually entered or generated via formulae etc...

VBA is 99.9% of the time written into the VB Editor (ALT + F11 from native XL)

We can help you with the code it was more a case of

a) do you want to use VBA ?

b) are you able to use VBA in your environment ?


Point b) is more an issue in network environments or where the files are to be distributed.
In most companies enabling VBA is not standard practice and on that basis requires intervention on the part of the user (at least once).
On that basis accounting for the possibility that VBA may not be enabled is normally worthy of consideration on the part of the "designer"

The only other thing worth noting is that Mac 2008 has no VBA support which may / may not be a concern.

Originally Posted by Rinocerinho

Well, I'd like to learn, as long as it's not so complicated (is it)? I'm completely unaware of the syntax, powers and limitations of this language.

Excel in it's own right is pretty vast so it follows that VBA follows suit.

I wouldn't say VBA is overly hard to get started with ... VBA is in itself a subset of VB and a lot of non-programmers find VB easier to follow / read than say C.

At the same time though VBA is not something you will become an expert in within a few days / weeks.
I've been coding (poorly) for the best part of ten years and what I don't know still far exceeds what I do know.

There are many advantages to learning VBA - from being able to reduce repetitive taks to nothing more than a press of a button to the more advanced things like being able to control other MS Apps remotely (ie control Outlook from Excel and vice versa)

However, it's often easy to get distracted with VBA and lose focus on the fact that the need for VBA can often be circumvented by good design in the first instance...

That said with VBA at your fingertips there really is very little you can't do with XL (rightly / wrongly).

If you search the forum for VBA Books etc you will find lots of good advice though don't lose sight of the fact that the Macro Recorder is perhaps the best learning tool available to you - ie press record - do what you want - stop recording - review the generated code... it might contain lots of superfluous info. but it will give the basic pointers in terms of syntax etc...


Getting back to the specifics of your question...

Where are the values/strings stored ? (you mentioned C2:C8 earlier, anywhere else ?)

How are they entered ? (manually or result of formulae)

Originally Posted by rinocerinho

are you sure this can only be accomplished with VBA?

If you're happy to manually set the font of each character in the string, no.
If you wish to automate the process, yes.
Conditional Formatting, Custom Formatting are applied at a cell level rather than at an individual character level within the cell itself.

In terms of a VBA process...

Please Login or Register  to view this content.

To use the above, right click on the tab name containing the values of interest, select View Code and paste the above into the resulting window.
Thereafter, in a Macro enabled file, you will find that altering the values in Column A will lead to them being formatted as per your wishes.
I would state that formatting font as white is obviously risky unless you have modified the background fill colour (to something other than those colours used above).

You will need to save the file in a macro supported type - eg .xlsm ... or if you want to run in Compatibility Mode save as old .xls

Modify ranges etc to suit your own requirements... the above is meant purely as an example.

The sheet into which you'll be entering the values you intend to have colour coded.

Assume that is Sheet1... in native XL right click on the tab Sheet1 and select View Code - paste the code provided into the resulting window.

Once in place this means that whenever you enter value(s) into Column A (adjust code to suit) the values will be colour coded on a character by character basis as and where they meet your criteria.

NOTE:
per my point on your other post regards UpperCase conversion ... if that forms part of this exercise you will need to merge the two Change routines as a given sheet object (eg Sheet1) can have only one Change event.