How to set color for text based on conditions?

[Eric]

Does anyone have any suggestions on how to set color for text based on
following conditions? For example, A1 = "Peter", A2 = "Pan"
CONCATENATE(A1,A2) in cell B1, I would like to set red color for A1, and blue
color for A2 under cell B1.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Mike H]

Hi,

You can't do that.

You could have the actual text values in the cell and by selecting each word
in the formula bar set them to different colours.

Mike

 

[Rick Rothstein]

As Mike said, you can't do that at the worksheet level; however, you can do
what you want using VBA event code. Right-click the tab at the bottom of the
worksheet you want this functionality on and select View Code from the popup
menu that appears; then copy/paste the following into the code window that
opened up when you did that...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
On Error GoTo RestartEvents
Application.EnableEvents = False
With Range("B1")
.Value = Range("A1").Value & Range("A2").Value
.Characters(1, Len(Range("A1").Value)).Font.ColorIndex = 3
.Characters(Len(Range("A1").Value) + 1).Font.ColorIndex = 5
End With
End If
RestartEvents:
Application.EnableEvents = True
End Sub

Okay, go back to your worksheet and enter some values into A1 and A2 and
watch what happens in B1.