Formatting result of Index function

E

ExcelFred

Hello,

I am using an Index formula to return translations set in defined cells.
I would like to highlight and hyperlink only given words returned by the
Index formula...and for the moment I do not manage to do it.
I have just access to formatting of the whole cell where index formula is
embedded.
The text to be returned by the indexed function is formatted as I want, but
the index formula just returns the value without taking care of the
formatting.

Can anyone give me a hand on this?

Thanks
 
E

ExcelFred

What do you mean by "Your format here"?
here is my formula =INDEX(Language!$D$6:$K$1231,17,$B$10)
in line 17 of array, column "B10.value" is the text I want to display with
the correct formatting ie sme words in the text are in red bold.

Following your advice, what should I do?
Define line 17 of array, column "B10.value" as text and on the calling cell
enter :
=TEXT(INDEX(Language!$D$6:$K$1231,17,$B$10),"what here")

Can you provide me with step by step instructions?
 
B

Bob Phillips

I was referring to returning your result in a format, such as #,##0.00 or
$0. I don't think you will get some red text in there, you would need to run
code on that.
 
E

ExcelFred

Well ok, that was my fear.
And I may be tricky as text todisplay in specific color and format are not
always at the same place.
Should I build a private function, an advanced Index function which does not
only returns value of the answer cell but also copy the formatting in full of
that answer cell and apply it to the cell embedding the Index function?
Is it possible to do so?
 
B

Bob Phillips

It's always possible if you have the desire. You cannot build a UDF to
format a cell, it just doesn't work. You either have a sub that goes around
afterwards and picks off the text and highlights it according to your rules,
or you use worksheet change event to monitor a cell, and then setup another
cell, including highlighted values.
 
Top