Changing colours of Part of string

I have a conditional formula which gives a value based on what the User specifies.
Example of formula is:
=IF($B$1=0,"CONTACT PERSON - OPTIONAL",IF($B$1=5,"CONTACT PERSON (FINANCE ONLY) - MANDATORY",""))
If the result of the string is " CONTACT PERSON (FINANCE ONLY) - MANDATORY " then
- word MANDATORY (not the whole string) should be in RED
- words (FINANCE ONLY) should be in BLUE
- remaining string should be left in BLANK
Not intending to use VBA.
Is this possible? Please help

Thanking in advance.

Rgds
Dhiresh

Hi there,

Pretty sure that without VBA this isn't going to happen. Have a Google - there seems to be a few VBA solutions out there.

HTH,

FermentedR

You could only do that with VBA - a formula would not be able to do it.

Pete

Hi Pete,
I am very new to Excel VBA. can you suggest a script I can use to get the results I need.

rgds
Dhiresh

Sorry, my skills don't stretch that far. Maybe someone else will come in.

Pete

You could try somethink like this.

Please Login or Register  to view this content.

where 7 is the start position of a string and 10 is the number of characters to color.

Alf

One more, see attachment, code courtesy of "jindon";
However it seems that "characters(..., ...).Font.Color" only works if the text in column C is an actual value, not the result of a formula?

Ya, you're right, it only works when it is text, with my code you would have to do a paste values into a new column before it would work.

HI..

I like to thx all who have given valueable assistance in this matter. I will try your suggestions

Rgds
Dhiresh

Hi All,
many thx for your suggested VBA to change colours of strings. I have adapted it for my purpose.

It is appreciated.

Kind Rgds
Dhiresh