If I hard code a text string in a cell, I can highlight part of the
cell and make the text red, for example, and then highlight the rest
of the text and make it blue.

I want to concatenate two text strings like =A1&" "&A2

I want what is in A1 to be red and what is in A2 to be blue.

I thought custom functions like =red(A1)&" "&blue(A2) might work, but
I can't figure out how to write the code.

Is a custom function the best option? Is there a simpler way? Is it
possible? Any help would be appreciated.

Thanks.
Angela

One way:

Instead of the formula, use an event macro. 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 sInsertAddr As String = "A3" 'change to suit
Dim sFirst As String
Dim sSecond As String
Dim nLen As Long

If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
sFirst = Range("A1").Text
nLen = Len(sFirst)
sSecond = Range("A2").Text
With Range(sInsertAddr)
.Value = sFirst & " " & sSecond
.Characters(1, nLen).Font.Color = RGB(255, 0, 0)
.Characters(nLen + 2).Font.Color = RGB(0, 0, 255)
End With
End If
End Sub


In article
<22571fcd-37f9-4b26-b956-(E-Mail Removed)>,
angelasg <(E-Mail Removed)> wrote:

> If I hard code a text string in a cell, I can highlight part of the
> cell and make the text red, for example, and then highlight the rest
> of the text and make it blue.
>
> I want to concatenate two text strings like =A1&" "&A2
>
> I want what is in A1 to be red and what is in A2 to be blue.
>
> I thought custom functions like =red(A1)&" "&blue(A2) might work, but
> I can't figure out how to write the code.
>
> Is a custom function the best option? Is there a simpler way? Is it
> possible? Any help would be appreciated.
>
> Thanks.
> Angela

hi
the techinque you are applying applies to text only. it does not work on
numbers or fomulas. reason....to have multiple formats in a cell requires
exact start and stop points. a formula like =A1 could return more than the
three characters of the formula.
here is quick code on how to do it with bad dog. bad = blue and dog = red
sub test()
ActiveCell.value = "bad dog"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Regular"
.ColorIndex = 5
End With
With ActiveCell.Characters(Start:=4, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=5, Length:=3).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = 3
End With
End Sub

yeah. you have to do spaces too.

regards
FSt1
"angelasg" wrote:

> If I hard code a text string in a cell, I can highlight part of the
> cell and make the text red, for example, and then highlight the rest
> of the text and make it blue.
>
> I want to concatenate two text strings like =A1&" "&A2
>
> I want what is in A1 to be red and what is in A2 to be blue.
>
> I thought custom functions like =red(A1)&" "&blue(A2) might work, but
> I can't figure out how to write the code.
>
> Is a custom function the best option? Is there a simpler way? Is it
> possible? Any help would be appreciated.
>
> Thanks.
> Angela
>

In article <038CF0AA-5EF7-443E-AFBA-(E-Mail Removed)>,
FSt1 <(E-Mail Removed)> wrote:

> yeah. you have to do spaces too.

Not really - a red space looks pretty much the same as a blue space.

Assume the data is in Cells A2 and B2 to be displayed in
cell C2.

Sub twoColr()
Dim x As Long, y As Long
x = Len(Range("A2").Value)
y = Len(Range("B2").Value)

myText = Range("A2").Value & Range("B2").Value
Range("C2") = myText
Range("C2").Characters(1, x).Font.ColorIndex = 3
Range("C2").Characters(Len(myText) - x + 1, y).Font.ColorIndex = 5
End Sub







"angelasg" wrote:

> If I hard code a text string in a cell, I can highlight part of the
> cell and make the text red, for example, and then highlight the rest
> of the text and make it blue.
>
> I want to concatenate two text strings like =A1&" "&A2
>
> I want what is in A1 to be red and what is in A2 to be blue.
>
> I thought custom functions like =red(A1)&" "&blue(A2) might work, but
> I can't figure out how to write the code.
>
> Is a custom function the best option? Is there a simpler way? Is it
> possible? Any help would be appreciated.
>
> Thanks.
> Angela
>

I recorded a macro of myself changing the formatting in a cell with
hard-coded text. What it gave me was pretty much what you have
below. I tried to adapt the code, but kept getting circular reference
errors. Unfortunately, this does not help in that the values in A1
and A2 will change so I can't use the text string as an alternative.

Thanks.

On Nov 2, 8:17*pm, FSt1 <F...@discussions.microsoft.com> wrote:
> hi
> the techinque you are applying applies to text only. it does not work on
> numbers or fomulas. reason....to have multiple formats in a cell requires
> exact start and stop points. *a formula like =A1 could return more than the
> three characters of the formula.
> here is quick code on how to do it with bad dog. bad = blue and dog =red
> sub test()
> ActiveCell.value = "bad dog"
> With ActiveCell.Characters(Start:=1, Length:=3).Font
> * * .Name = "Arial"
> * * .FontStyle = "Regular"
> * * .ColorIndex = 5
> End With
> With ActiveCell.Characters(Start:=4, Length:=1).Font
> * * .Name = "Arial"
> * * .FontStyle = "Regular"
> * * .Size = 10
> * * .ColorIndex = xlAutomatic
> End With
> With ActiveCell.Characters(Start:=5, Length:=3).Font
> * * .Name = "Arial"
> * * .FontStyle = "Regular"
> * * .Size = 10
> * * .ColorIndex = 3
> End With
> End Sub
>
> yeah. you have to do spaces too.
>
> regards
> FSt1
>

I will try this when I get to work tomorrow. Thanks.

On Nov 2, 8:06*pm, JE McGimpsey <jemcgimp...@mvps.org> wrote:
> One way:
>
> Instead of the formula, use an event macro. 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 sInsertAddr As String = "A3" 'change to suit
> * * * * Dim sFirst As String
> * * * * Dim sSecond As String
> * * * * Dim nLen As Long
>
> * * * * If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
> * * * * * * sFirst = Range("A1").Text
> * * * * * * nLen = Len(sFirst)
> * * * * * * sSecond = Range("A2").Text
> * * * * * * With Range(sInsertAddr)
> * * * * * * * * .Value = sFirst & " " & sSecond
> * * * * * * * * .Characters(1, nLen).Font.Color = RGB(255, 0, 0)
> * * * * * * * * .Characters(nLen + 2).Font.Color = RGB(0, 0, 255)
> * * * * * * End With
> * * * * End If
> * * End Sub
>