Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make 1st word in cell bold after combining words from two cells
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make 1st word in cell bold after combining words from two cells
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 " wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make 1st word in cell bold after combining words from two cells
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) wrote in message 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make 1st word in cell bold after combining words from two cells
On Jun 5, 12:01*am, "Jim Cone" wrote:
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) wrote in message 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make 1st word in cell bold after combining words from two cell
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 " wrote: On Jun 5, 12:01 am, "Jim Cone" wrote: 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) wrote in message 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make 1st word in cell bold after combining words from two cell
On Jun 5, 1:22*am, Joel wrote:
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 " wrote: On Jun 5, 12:01 am, "Jim Cone" wrote: 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) wrote in message 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.- Hide quoted text - - 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?? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make 1st word in cell bold after combining words from two cell
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?? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make 1st word in cell bold after combining words from two cell
The problem is when you use a formula (=A1&B2) the formating doesn't work.
the code works if you have straight text. " wrote: On Jun 5, 1:22 am, Joel wrote: 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 " wrote: On Jun 5, 12:01 am, "Jim Cone" wrote: 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) wrote in message 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.- Hide quoted text - - 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?? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make 1st word in cell bold after combining words from two cells
On Wed, 4 Jun 2008 20:45:41 -0700 (PDT), "
wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make 1st word in cell bold after combining words from two cells
On Jun 5, 7:08*am, Ron Rosenfeld wrote:
On Wed, 4 Jun 2008 20:45:41 -0700 (PDT), " wrote: 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- 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make 1st word in cell bold after combining words from two cells
On Thu, 5 Jun 2008 07:02:17 -0700 (PDT), "
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to concatonate two words with first word in BOLD format? | Excel Discussion (Misc queries) | |||
Can i make text in cells c60@n60 bold & black if sum of C59 is gre | Excel Worksheet Functions | |||
How do i make certain words automatically bold themselves. | New Users to Excel | |||
Is it possible to make a word in a formula bold? | Excel Worksheet Functions | |||
Check for text within cells and make bold | Excel Programming |