Concatenate 2 cells, and have one cell value display as bold?

P

Pierre

Looking to join within the same cell the contents of two cells, and
have lets say B1, display as is formatted in the originating cell?

A1=Happy
B1=Birthday (bolded)
A2(the concatenating cell) will read "Happy Birthday" (with BIRTHDAY
in bold) Happy remains unbolded.

I found some code in another post to this group, but couldn't get it
to work.

TIA for any thoughts.

Pierre
 
D

Duke Carey

You can format specific characters within a text string to be bold. However,
a formula is NEVER a text string to Excel. That means, if you want to do
what you are talking about, you'll need to write your own, non-trivial, VBA
code to combine and format the strings.
 
R

Ron Rosenfeld

Looking to join within the same cell the contents of two cells, and
have lets say B1, display as is formatted in the originating cell?

A1=Happy
B1=Birthday (bolded)
A2(the concatenating cell) will read "Happy Birthday" (with BIRTHDAY
in bold) Happy remains unbolded.

I found some code in another post to this group, but couldn't get it
to work.

TIA for any thoughts.

Pierre

As far as I know, you can only have differential formatting on a text string.
So you would have to do your "concatenation" in a VBA routine, which would
right a differentially formatted text string to the target cell.

One method would be to use an event-triggered macro. But merely changing the
font of a cell does not trigger a "change" event.

Right click the sheet tab.
Select View Code
Paste the code below into the window that opens.

=======================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rRes As Range
Dim sTemp As String
Dim rSrc1 As Range, rSrc2 As Range
Dim bSrc1Bold As Boolean
Dim bSrc2Bold As Boolean

Set rRes = [a5]
Set rSrc1 = [a1]
Set rSrc2 = [b1]

sTemp = rSrc1 & " " & rSrc2

With rRes
.Value = sTemp
.Characters(1, Len(rSrc1.Text)).Font.Bold = rSrc1.Font.Bold
.Characters(Len(rSrc1.Text) + 2, Len(rSrc2.Text)).Font.Bold =
rSrc2.Font.Bold
End With

End Sub
=====================================


--ron
 
P

Pierre

Looking to join within the same cell the contents of two cells, and
have lets say B1, display as is formatted in the originating cell?
A1=Happy
B1=Birthday (bolded)
A2(the concatenating cell) will read "Happy Birthday" (with BIRTHDAY
in bold) Happy remains unbolded.
I found some code in another post to this group, but couldn't get it
to work.
TIA for any thoughts.

As far as I know, you can only have differential formatting on a text string.
So you would have to do your "concatenation" in a VBA routine, which would
right a differentially formatted text string to the target cell.

One method would be to use an event-triggered macro. But merely changing the
font of a cell does not trigger a "change" event.

Right click the sheet tab.
Select View Code
Paste the code below into the window that opens.

=======================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rRes As Range
Dim sTemp As String
Dim rSrc1 As Range, rSrc2 As Range
Dim bSrc1Bold As Boolean
Dim bSrc2Bold As Boolean

Set rRes = [a5]
Set rSrc1 = [a1]
Set rSrc2 = [b1]

sTemp = rSrc1 & " " & rSrc2

With rRes
.Value = sTemp
.Characters(1, Len(rSrc1.Text)).Font.Bold = rSrc1.Font.Bold
.Characters(Len(rSrc1.Text) + 2, Len(rSrc2.Text)).Font.Bold =
rSrc2.Font.Bold
End With

End Sub
=====================================

--ron

Ron,

Getting a syntax error, with "Private Sub Worksheet. . ." in yellow,
and
".Characters(Len. . ." line appearing in red from the paste.

Entering any characters into A5, A1, or B1 will produce the error.

Thanks for your time and effort.

Pierre
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top