Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Specify a font from a formula?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Specify a font from a formula?
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 -- Regards, OssieMac "Jo" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Specify a font from a formula?
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 "OssieMac" wrote: 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 -- Regards, OssieMac "Jo" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Choose Font In A Formula | Excel Discussion (Misc queries) | |||
help with formula for bolding font | Excel Worksheet Functions | |||
Edit font within a formula? | Excel Discussion (Misc queries) | |||
Font selection within formula | Excel Worksheet Functions | |||
Font in Formula Bar | Excel Discussion (Misc queries) |