Preserving formatting in a formula.

[Guest]

I have a list of shipping codes and descriptions that is referenced in a
template. I need to copy the list and descriptions and add text to the
beginning of the description. The formula ="Customer "&B2" does this but I
want Customer to be Bold and Red. I tried putting Customer in its own set of
cells setting it to Bold, Red and using =B2&" "&C2 but it does not carry over
the formatting. How can I do this?

 

[Guest]

You need to copy the formulas and paste/special/value elsewhere. The result
will be a text string. You can then format individual characters

 

[Guest]

That would be a thought but I would have to highlight the first 10-15
characters of every cell one at a time and change the format.. I would have
to do this ~400 times... I just thought there might be an easy way to get the
formatting to carry over from one cell to the combined formula..

 

[Dave Peterson]

How about changing the values right where they sit.

Select the range and run this macro:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range
Dim myPFX As String

myPFX = "Customer: "

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, Selection.Cells.SpecialCells(xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Select some cells with values!"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
.Value = myPFX & .Text
With .Characters(Start:=1, Length:=Len(myPFX))
.Font.ColorIndex = 3
.Font.Bold = True
End With
End With
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

..colorindex = 3
is red in my workbook. It doesn't have to be that for your workbook (but
probably is).