Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I change font attributes in a Concatenate statement?
Excel 2002.
I need to change fonts and font colour, within the one cell. For example, begin the cell with a Red Wingding Bullet at 10pt, followed by an Item Description in Arial Black 10pt, followed by a Sub-Description in Black in say 8Pt Arial. E.g. - Adjustable Paper Tray (A6 to A3) When you are typing directly into a cell in Excel, you can do this quite easily. However, when the cell needs to be populated with a formula, I cannot find a way to do this. What is needed is some sort of coding, like the [Red] code you can use in Number Formats, but it needs to be an extension of this concept. For example, in a Concatenate statement to build up a cell from three separate elements, such as "-"," Adjustable Paper Tray"," (A4-A3)" it should be possible to say something like [Bold][Red][Wingding][10pt]"-",[Regular][Black][Arial]" Adjustable Paper Tray",[8pt]" (A4-A3)" to achieve the same font & colour control in a formula as you can when typing directly into the cell. Is there any way of doing this in Excel as it is? If not,is there any chance of incorporating such a function into the next version? |
#2
|
|||
|
|||
You can't do this in XL. However you can do it with VBA Event macros.
Put this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sPREFIX As String = "- Adjustable Paper Tray " If Not Intersect(Target, Range("A3:A4")) Is Nothing Then With Range("A5") .ClearFormats Application.EnableEvents = False .Value = sPREFIX & Format(Range("A4").Value - _ Range("A3").Value, "0.00") Application.EnableEvents = True With .Characters(1, 1).Font .Name = "Wingdings" .Color = vbRed .Size = 10 End With With .Characters(2, Len(sPREFIX) - 1).Font .Name = "Arial" .Color = vbBlack .Size = 10 End With With .Characters(Len(sPREFIX) + 1).Font .Name = "Arial" .Color = vbBlack .Size = 8 End With End With End If End Sub Now whenever an entry is made in A3 or A4, A5 (change to suit) will contain the formatted entry. In article , "DHD58" wrote: Is there any way of doing this in Excel as it is? If not,is there any chance of incorporating such a function into the next version? |
#3
|
|||
|
|||
Tks for your time, JEMcG. Much appreciated.
I was hoping Microsoft might pick up on this for a future inclusion (font attribute control within Excel Worksheet Functions) as it would be very useful. I suspected VBA would be needed with the current Excel version. I didn't give you enough detail in my post to provide a VBA solution. While I'm very competent with worksheet functions, I'm in the beginner class with VBA, so was unable to modify your suggestion to provide the solution. If you are able to help further, if I provide more details, please reply to the post, with a method of sending you a sample worksheet. Not sure how we would do that without bringing down lots & lots of spam on our heads. Tks David H "JE McGimpsey" wrote: You can't do this in XL. However you can do it with VBA Event macros. Put this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sPREFIX As String = "- Adjustable Paper Tray " If Not Intersect(Target, Range("A3:A4")) Is Nothing Then With Range("A5") .ClearFormats Application.EnableEvents = False .Value = sPREFIX & Format(Range("A4").Value - _ Range("A3").Value, "0.00") Application.EnableEvents = True With .Characters(1, 1).Font .Name = "Wingdings" .Color = vbRed .Size = 10 End With With .Characters(2, Len(sPREFIX) - 1).Font .Name = "Arial" .Color = vbBlack .Size = 10 End With With .Characters(Len(sPREFIX) + 1).Font .Name = "Arial" .Color = vbBlack .Size = 8 End With End With End If End Sub Now whenever an entry is made in A3 or A4, A5 (change to suit) will contain the formatted entry. In article , "DHD58" wrote: Is there any way of doing this in Excel as it is? If not,is there any chance of incorporating such a function into the next version? |
#4
|
|||
|
|||
While I may be able to help, the nice thing about these newsgroups is
that there are lots people who can provide assistance, many of whom are more knowledgeable than I am. In general, I don't accept worksheets from non-clients. In addition to virii, I find that much of the time, they're not very helpful - what seems crystal clear to the original poster, is murky at best to anyone else. Certainly the effort to reduce the problem to text puts most of the burden of defining the problem on the one who's seeking help. I would suggest posting a text description of what you're trying to accomplish. In article , DHD wrote: If you are able to help further, if I provide more details, please reply to the post, with a method of sending you a sample worksheet. Not sure how we would do that without bringing down lots & lots of spam on our heads. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change font in a forms control combo box | Excel Discussion (Misc queries) | |||
How do I change default font for all new comments in Office 2003? | Excel Discussion (Misc queries) | |||
How do I get the font color to change automatically depending on | Excel Discussion (Misc queries) | |||
How to change the default font and size of "comments"? | Excel Discussion (Misc queries) | |||
Change Font Size | Excel Worksheet Functions |