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?

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 <[email protected]>,
"DHD58" <[email protected]> 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?

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 <[email protected]>,
> "DHD58" <[email protected]> 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?
>

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 <[email protected]>,
DHD <[email protected]> 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.