Specify a font from a formula?

[Jo]

Is there a way to have more than one font in the same cell, when the contents
are generated by a function? In other words, is there any way to control the
font of a specific character in a cell through a function? Conditional
formatting won't do this because it will apply only to part of the cell
contents, not the whole cell.

I am using a special font for certain symbols, but I want to combine these
with regular text in the same cell. For example, say I want to combine an
arrow from Wingdings (è character) with the text of a date "28-Mar-2009" in
another font (e.g. Arial).

I can do it if I type it all directly into a cell -- I can block out the è
and select Wingdings as the font, and it will format just that one character
and leave the rest of the contents in the original font. So I keep thinking
there must be a way to wrap the special character inside a function that
would specify a particular font, but I haven't found anything like that so
far.

Does anyone know of a way to do this in a formula?

Thanks in advance for the help.

 

[OssieMac]

Hi Jo,

I don't know whether the following will help but try it and see what it
does. it is a worksheet change event that looks for the special character,
CHAR(232), which is the è character and it converts it to Wingdings and then
applies bold and red color.

It has some short comings. It can find the character in a formula in a cell
but it cannot format the character in the formula output value. Therefore, if
it finds the character then it copies the cell and pastes special values to
remove the formula. If removing the formula is a problem then it is simply
not going to work for you.

Did you know that when you are inserting the character during typing you can
just hold the Alt key down and type 0232 on the numeric keypad. (Must use the
numeric keypad; does not work with the numerics along the top of the keypad)

I suggest that you test it on a blank new workbook and see whether it does
what you want before installing it in your project.

Don't know how much info you need to install the macro so I'll go for
overkill instead of you having to get back to me.

To insert the code:-
Right click on the worksheet name tab.
Select View Code
Copy and paste the code into the VBA editor.
Hold Alt key and press F11 to return to worksheet.
Save the workbook. (If xl2007 then must use save as and select Macro Enabled
workbook (xlsm).
You will need to have macros enabled in the Options. See Help for how.
In xl2007 recommend set macro security to Disable all macros with
notification.
Earlier versions recommend set to Medium security.

You can delete the macro by opening the VBA editor as above and simply
deleting all the code in the VBA editor.


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

Dim intChrPos As Integer

intChrPos = InStr(1, Target.Value, Chr(232))

If intChrPos > 0 Then 'Special character found
Target.Copy
Target.PasteSpecial Paste:=xlPasteValues

With Target.Characters _
(Start:=intChrPos, Length:=1).Font

.Name = "Wingdings"
.Bold = True
.Color = vbRed
End With
End If

Application.EnableEvents = True

End Sub

 

[Jo]

Hi OssieMac,

Thanks very much for your reply. I was hoping I could do it without having
to resort to a macro, as it just opens up other complications for the
application I'm doing. But thanks very much for taking the time to give me
the macro code!

The actual font I'm using is a specialty font (not Wingings; I was just
using that as an example) that does have regular alphabet characters, so I've
just changed the font of the whole cell to that font. I was hoping to be able
to combine the special characters with another font (e.g. Arial condensed, to
compact the width of the cell) but I've managed to work around it. It's not
the optimal solution but it works.

Again, thanks so much for your help!

Jo