On Mon, 27 Oct 2008 06:45:01 -0700, jday
wrote:

I have a formula in cell B2 that concatenates a combination of text &
contents from other cells in the worksheet. In the table below, cell C2
represents the color, cell D2 is the type of item, and cell E2 is the
quantity in inventory. For example:

C2 D2 E2
---- ---- ----
Blue Rug 3

The concatenation formula in cell B2 looks like this:

=C2&" "&D2&" - Qty ("&E2&")"

So that the result in B2 looks like this: Blue Rug - Qty (3)

What I would like is to format the "Qty (3)" portion of the text result to
be bold, or a different color, or italicized, or somehow highlighted to make
it stand out from the rest of the text in that cell. Is there a special
format code that I can insert right before each of the three components that
make up the " - Qty (3)" part of the text to make that happen?



That cannot be done with a formula in the cell.

However, you could use a macro to accomplish the concatenation; write the
result to the cell as a text string, and differentially format the Qty (3)
portion.

If the entries in C, D and e are entered manually as text, then something like
below might work for you:

right click on the sheet tab
select View Code
Paste the code below into the window that opens.

Make sure the range for data entry is the correct size.

================================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Dim sRes As String
Dim lQtyStart As Long, lQtyLen As Long
Application.EnableEvents = False
Set r = Range("C2:E100") 'set to data entry area

If Not Intersect(Target, r) Is Nothing Then
For Each c In Target
If Not Intersect(c, r) Is Nothing Then
If Application.WorksheetFunction.CountA _
(Range(Cells(c.Row, 3), Cells(c.Row, 5))) = 3 Then
sRes = Cells(c.Row, 3).Text & _
" " & Cells(c.Row, 4).Text & _
" - Qty (" & Cells(c.Row, 5).Text _
& ")"
lQtyStart = InStrRev(sRes, "(") + 1
lQtyLen = InStrRev(sRes, ")") - lQtyStart
With Cells(c.Row, 2)
.Value = sRes
.Characters(lQtyStart, lQtyLen).Font.Bold = True
.Characters(lQtyStart, lQtyLen).Font.Color = vbGreen
End With
Else
Cells(c.Row, 2).Value = ""
End If
End If
Next c
End If
Application.EnableEvents = True
End Sub
========================================

--ron