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
,
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

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 ,
FSt1 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 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 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