Make 1st word in cell bold after combining words from two cells

[RJQMAN]

I am combining the words from two cells, and I would like to change
the font of the first word in the combined cell. However, I keep
getting the font of the entire cell to change.

The cell has this formula, which draws text from two other pages in
the workbook, and it works fine

=Paragraphs!C8&" "&Details!B2

The first cell has about 60 characters in it, and the second cell
about 30 or so. The length of the first cell never varies, but the
length of the second cell does.

OK. So now the correct text is in the target cell. Now I want to
make the first word bold-face. I could not figure out how to do it
without VBA, so I tried to do this in single statement in a VBA
subroutine. It caused the entire cell to turn bold face..

ActiveCell.Characters(Start:=1, Length:=1).Font.FontStyle = "Bold"

What can I do? Any help is appreciated.

 

[Joel]

Words = Range("A1")
FirstSpace = InStr(Words, " ")
WordLen = FirstSpace - 1

With Range("A1").Characters(Start:=1, Length:=WordLen).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Range("A1").Characters(Start:=FirstSpace, Length:=(Len(Words) -
WordLen)).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

 

[Jim Cone]

Sub FewAreBold()
Dim N As Long
N = InStr(1, ActiveCell.Text, " ", vbTextCompare)
ActiveCell.Characters(1, N).Font.Bold = True
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



I am combining the words from two cells, and I would like to change
the font of the first word in the combined cell. However, I keep
getting the font of the entire cell to change.
The cell has this formula, which draws text from two other pages in
the workbook, and it works fine
=Paragraphs!C8&" "&Details!B2
The first cell has about 60 characters in it, and the second cell
about 30 or so. The length of the first cell never varies, but the
length of the second cell does.
OK. So now the correct text is in the target cell. Now I want to
make the first word bold-face. I could not figure out how to do it
without VBA, so I tried to do this in single statement in a VBA
subroutine. It caused the entire cell to turn bold face..

ActiveCell.Characters(Start:=1, Length:=1).Font.FontStyle = "Bold"

What can I do? Any help is appreciated.

 

[RJQMAN]

Sub FewAreBold()
  Dim N As Long
  N = InStr(1, ActiveCell.Text, " ", vbTextCompare)
  ActiveCell.Characters(1, N).Font.Bold = True
End Sub
--
Jim Cone
Portland, Oregon  USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


I am combining the words from two cells, and I would like to change
the font of the first word in the combined cell.  However, I keep
getting the font of the entire cell to change.
The cell has this formula, which draws text from two other pages in
the workbook, and it works fine
=Paragraphs!C8&" "&Details!B2
The first cell has about 60 characters in it, and the second cell
about 30 or so.  The length of the first cell never varies, but the
length of the second cell does.
OK.  So now the correct text is in the target cell.  Now I want to
make the first word bold-face.  I could not figure out how to do it
without VBA, so I tried to do this in single statement in a VBA
subroutine.  It caused the entire cell to turn bold face..

ActiveCell.Characters(Start:=1, Length:=1).Font.FontStyle = "Bold"

What can I do?  Any help is appreciated.

I have tried the approach suggested by Jim, and the entire cell turns
bold face. Then I tried the longer approach suggested by Joel, and
nothing happens at all. Joel, I did change to cell reference to the
cell in question that contains the combined text. Excel does not seem
to recognize the first word in the combined text box.

 

[Joel]

Jim and my approach are really identical. I just have some additional
options that can be eliminated and added a method if you want to change the
rest of the cell.

I think the problem is the activecell is not the activecell but some other
cell. Or it should be FontStyle instead of Font. try modifying Jim's code
as follows

Sub FewAreBold()
Dim N As Long
N = InStr(1, Range("A1").Text, " ", vbTextCompare)
Range("A1").Characters(1, N)..FontStyle.Bold = True
End Sub

 

[RJQMAN]

Jim and my approach are really identical.  I just have some additional
options that can be eliminated and added a method if you want to change the
rest of the cell.

I think the problem is the activecell is not the activecell but some other
cell.   Or it should be FontStyle instead of Font.  try modifying Jim's code
as follows

Sub FewAreBold()
  Dim N As Long
  N = InStr(1, Range("A1").Text, " ", vbTextCompare)
  Range("A1").Characters(1, N)..FontStyle.Bold = True
End Sub






- Show quoted text -

I am sorry, I run into the exact same problem. I changed the reference
cell from A1 to the cell I am working in, D17, and once again the
entire cell turns bold.

For more reference, the reference cell is drawing from two other
cells. One cell (a1) contains this phrase - "Property: The property
is located at" and the second combined cell (B2) says "15 Elm
Street." Cell D17 contains the formula. The combined cell, D17,
reads "Property: The property is located at 15 Elm Street." It all
works fine, except I would like to change the first word text to Bold
Face...I hope this clarifies. I am up against a wall ad do not
understand why...

Does this help??

 

[Tim Williams]

This will only work on a cell which actually contains text.
If the cell contains a formula then I don't think it's possible.

Tim



I am sorry, I run into the exact same problem. I changed the reference
cell from A1 to the cell I am working in, D17, and once again the
entire cell turns bold.

For more reference, the reference cell is drawing from two other
cells. One cell (a1) contains this phrase - "Property: The property
is located at" and the second combined cell (B2) says "15 Elm
Street." Cell D17 contains the formula. The combined cell, D17,
reads "Property: The property is located at 15 Elm Street." It all
works fine, except I would like to change the first word text to Bold
Face...I hope this clarifies. I am up against a wall ad do not
understand why...

Does this help??

 

[Joel]

The problem is when you use a formula (=A1&B2) the formating doesn't work.
the code works if you have straight text.

 

[Ron Rosenfeld]

I am combining the words from two cells, and I would like to change
the font of the first word in the combined cell. However, I keep
getting the font of the entire cell to change.

The cell has this formula, which draws text from two other pages in
the workbook, and it works fine

=Paragraphs!C8&" "&Details!B2

The first cell has about 60 characters in it, and the second cell
about 30 or so. The length of the first cell never varies, but the
length of the second cell does.

OK. So now the correct text is in the target cell. Now I want to
make the first word bold-face. I could not figure out how to do it
without VBA, so I tried to do this in single statement in a VBA
subroutine. It caused the entire cell to turn bold face..

ActiveCell.Characters(Start:=1, Length:=1).Font.FontStyle = "Bold"

What can I do? Any help is appreciated.

You can't do that in Excel with a formula in the cell. You can only
differentially format characters in a cell if the cell contains a text string.

So you will need to execute your formula in a VBA macro; store the result in
the desired cell as a text string, and then do your differential format.

Without knowing more details, it's hard to know the best way, but something
like the following should give you some ideas as to how to approach the problem

===========================================
Option Explicit
Sub Para()
Dim rSrc1 As Range, rSrc2 As Range
Dim rDest As Range
Dim lFirstWord As Long

Set rSrc1 = Worksheets("Sheet2").Range("B2")
Set rSrc2 = Worksheets("Sheet3").Range("C8")
Set rDest = Worksheets("Sheet1").Range("A1")

With rDest
.Value = rSrc1.Value & " " & rSrc2.Value
lFirstWord = InStr(1, .Value, " ") - 1
.Characters(1, lFirstWord).Font.Bold = True
End With
End Sub
=====================================
--ron

 

[RJQMAN]

You can't do that in Excel with a formula in the cell.  You can only
differentially format characters in a cell if the cell contains a text string.

So you will need to execute your formula in a VBA macro; store the result in
the desired cell as a text string, and then do your differential format.

Without knowing more details, it's hard to know the best way, but something
like the following should give you some ideas as to how to approach the problem

===========================================
Option Explicit
Sub Para()
Dim rSrc1 As Range, rSrc2 As Range
Dim rDest As Range
Dim lFirstWord As Long

    Set rSrc1 = Worksheets("Sheet2").Range("B2")
    Set rSrc2 = Worksheets("Sheet3").Range("C8")
    Set rDest = Worksheets("Sheet1").Range("A1")

With rDest
    .Value = rSrc1.Value & " " & rSrc2.Value
    lFirstWord = InStr(1, .Value, " ") - 1
    .Characters(1, lFirstWord).Font.Bold = True
End With
End Sub
=====================================
--ron- Hide quoted text -

- Show quoted text -

RON - Thanks so much. That solved my problem! I appreciate all who
responded. Would somebody out there please write a decent book on how
to use this amazing program!? I have purchased 5 or 6 books that all
turn out to never have the information I really need. Without this
News Group, I would be lost. Thanks many times over.

 

[Ron Rosenfeld]

RON - Thanks so much. That solved my problem! I appreciate all who
responded. Would somebody out there please write a decent book on how
to use this amazing program!? I have purchased 5 or 6 books that all
turn out to never have the information I really need. Without this
News Group, I would be lost. Thanks many times over.

You're welcome. Glad to help. Thanks for the feedback. And no, I don't know
of any books on Excel or VBA; I'm mostly self-taught, and these NG's have been
invaluable.
--ron