A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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



 
 
Thread Tools Display Modes
  #1  
Old April 24th 07, 07:43 PM posted to microsoft.public.excel.worksheet.functions
Pierre
external usenet poster
 
Posts: 193
Default Concatenate 2 cells, and have one cell value display as bold?

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

Ads
  #2  
Old April 24th 07, 09:18 PM posted to microsoft.public.excel.worksheet.functions
Duke Carey
external usenet poster
 
Posts: 1,081
Default Concatenate 2 cells, and have one cell value display as bold?

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.




"Pierre" wrote:

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

  #3  
Old April 24th 07, 09:23 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 5,651
Default Concatenate 2 cells, and have one cell value display as bold?

On 24 Apr 2007 11:43:03 -0700, Pierre > wrote:

>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
  #4  
Old April 26th 07, 02:04 PM posted to microsoft.public.excel.worksheet.functions
Pierre
external usenet poster
 
Posts: 193
Default Concatenate 2 cells, and have one cell value display as bold?

On Apr 24, 3:23 pm, Ron Rosenfeld > wrote:
> On 24 Apr 2007 11:43:03 > wrote:
>
> >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


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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Join bold and non-bold text in one cell bkincaid Excel Discussion (Misc queries) 3 March 21st 06 12:58 AM
In Excel 2003, I cannot display the toolbar with BOLD,UNDERLINE EJ Setting up and Configuration of Excel 2 March 15th 06 04:24 PM
concatenate cells from a column and paste to a cell washdcjohn Excel Discussion (Misc queries) 3 November 15th 05 09:11 PM
Can you keep a cells BOLD Font, cell color size Trese Excel Discussion (Misc queries) 2 August 23rd 05 03:09 PM
How can I cause a cell which is hyperlinked to to display in bold. Chris G. Excel Worksheet Functions 0 January 6th 05 03:15 PM


All times are GMT +1. The time now is 08:47 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.