different color text in the same cell

[angelasg]

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

 

[JE McGimpsey]

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

 

[FSt1]

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

 

[JE McGimpsey]

yeah. you have to do spaces too.

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

 

[JLGWhiz]

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]

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.

 

[angelasg]

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