Excel Highlighting Problem

Question

Hello,

So let me explain my issue: I want to highlight part of a cell that contains a function. To be clearer, I'll take an example:

=concatenate("Text 1 ";if(B1=1;"TRUE";"FALSE");" Text 2") --> In this example, I would like to highlight in red only "TRUE" or "FALSE" and not the rest of the characters (Text 1 and 2)

Thanks in advance for your answers

Configuration: Windows XP / Internet Explorer 7.0

MarcFsB · Answer

Should the red highlighting be done in the formula or in the result display Marc

Mike-31 · Answer

Hi,  the result from a formula cannot be partially formatted manually or by formula, to my knowledge, only in VBA A+ Mike-31  A period of failure is an ideal time to sow the seeds of knowledge.

Mike-31 · Answer

Re,

Right-click on the tab of the concerned sheet/View Code
paste this code into the blank sheet, as soon as the value 1 or 2 is entered in column B on the same row, "Text 1 TRUE Text 2" will be written in column D
or "Text 1 FALSE Text 2"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As String
Dim place As Byte
Dim i As Byte
If Target.Column <> 2 Then Exit Sub
If Target = 1 Then
t = "Text 1 " & "TRUE" & " Text 2"
place = InStr(3, t, "TRUE")
With Target.Offset(0, 2)
.Value = t
For i = place To place + Len("TRUE")
.Characters(i, 1).Font.Underline = xlUnderlineStyleSingle
Next i
End With
Else
t = "Text 1 " & "FALSE" & " Text 2"
place = InStr(3, t, "FALSE")
With Target.Offset(0, 2)
.Value = t
For i = place To place + Len("FALSE")
.Characters(i, 1).Font.Underline = xlUnderlineStyleSingle
Next i
End With
End If
End Sub

I'm looking for a color highlighting
A+
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

eriiic · Answer

Hello,  which version of Excel? In 2003, you can change the font color but not the background of part of a string. Moreover, this string must be entered and not the result of a formula.  eric

Mike-31 · Answer

Re,

Here is the code with TRUE or FALSE underlined in red; it is still possible to vary the color and add bold

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As String
Dim place As Byte
Dim i As Byte
If Target.Column <> 2 Then Exit Sub
If Target = 1 Then
t = "Text 1 " & "TRUE" & " Text 2"
place = InStr(3, t, "TRUE")
With Target.Offset(0, 2)
.Value = t
For i = place To place + Len("TRUE")
.Characters(i, 1).Font.ColorIndex = 3
.Characters(i, 1).Font.Underline = xlUnderlineStyleSingle
Next i
End With
Else
t = "Text 1 " & "FALSE" & " Text 2"
place = InStr(3, t, "FALSE")
With Target.Offset(0, 2)
.Value = t
For i = place To place + Len("FALSE")
.Characters(i, 1).Font.ColorIndex = 3
.Characters(i, 1).Font.Underline = xlUnderlineStyleSingle
Next i
End With
End If
End Sub

See you +
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

Raymond PENTIER · Answer

Well now, Plato, you asked for it! -- Retirement is great! Especially in the Caribbean... :-) &#9787; Raymond &#9794;

Excel Highlighting Problem

6 réponses

How to get the character µ on the android keyboard.

Texture issues in enshrouded

Access issue to my "contact me" profile

Windows spotlight is not working.

Black screen digital camera

Connecting rj45 cable with fiber

Sony bravia tv red led blinks 4 times

Free: associating with the freebox server in progress

Inuyasha in french

Ccleaner starts at boot.