Format a subset of text in Excel Cell Using Formula

Question

I Have built a string using a formula in excel. as an example

Cell C3 contains text "Languages"
Cell C4 = "English, Spanish,German, French"
My Forumla = C3 & ":" & CHAR(10) & C4

The Desired text would be:

Languages:
English, Spanish, German, French

(where the bold text would actually be some color like red)

Is there a way to do this in Excel (change partial text formatting) .

I Have tried a formula... (Not working)

Function formatText(InText As Range)

'Set font color
  InText.Characters(1.5).Font.Color = Red
   'InText.Characters((InStr(1, ":", InText) + 1), (Len(InText) - InStr(1, ":", InText))).Font.ColorIndex = 3
End Function

Answer 1

Your posted function with work if and only if

• It is called from a Sub (ie, as other have mentioned, not as a UDF)

And

• the value(s) contained in range InText are string constants. (This is the main point of my answer)

It will not work for any cells in range InText containing a formula. AFAIK you cannot format part of a string returned by a formula.

BTW I would love to be proved wrong on this!

Answer 2

Regarding Hightower's question, "how would you cast a formula output to a string so that you can apply the text formatting?"

To "cast" the output of a formula so that you can apply text formatting, you must write the value returned by the formula into the spreadsheet, then apply the formatting to the value you wrote. You could either write the value into the cell containing the formula (which will erase the formula), or you could write the value into a different place in the spreadsheet (which will preserve the formula but then you'll be seeing double).

Sub Cell_Format(InText as Range)
  InText.formula = cstr(InText.value)  ' converts result of formula into a string literal   
    'or: InText.offset(0,1).formula = cstr(InText.value) -- writes the value in the cell next to InText
  InText.characters(1, 5).font.color = vbRed
End Sub

Then Cell_Format range("$A$1") will replace the formula in cell $A$1 with a string constant and change the color of the first five characters to red.

If you want to do this for a range larger than one cell, add this code to the above:

Sub Range_Format(InText as Range)
  For each c in InText
    Cell_Format(c)
  Next
End Sub

Answer 3

You need to use this code:

InText.Characters(1,5).Font.Color = RGB(255, 0, 0)

If you want to make it flexible, so that only the (fully) second line is red, use this code:

InText.Characters(Instr(InText, vbCr)+1, Len(InText)-Instr(InText, vbCr)).Font.Color = RGB(255, 0, 0)

Note that your function needs to be called from VBA, i.e. you cannot use it as a User-Defined-Function! UDFs can only return a result but never modify a cell!

Answer 4

You cannot directly call the below UDF in Excel interface. For that you will have use an event as UDF cannot change the physical characteristic of a cell. For more details you may read this link. http://support.microsoft.com/kb/170787

Function formatText(InText As Range)

'Set font color
  InText.Interior.Color = vbRed
   'InText.Characters((InStr(1, ":", InText) + 1), (Len(InText) - InStr(1, ":", InText))).Font.ColorIndex = 3
End Function