Hi I am have having problem applying bold format within string text. I want to bold the Input!10 data pulled from another sheet. is there a way to do this? is it even possible?

="your application will be renewing on"&" "&TEXT(Input!B10, "mmmm dd, yyyy.")&" " & "In an effort to furnish you with the coverage that"



dan

hi,
yes you can do that. in a regular text string. the code below show how it's
done.
but you can't do that in a formula. reason being you have to have exact
start and stop points and exact lengths in order to have different formats
within the same cell and formulas can produce varing results. sorry.

Sub differentformats()
'this bolds the word you and leave the other words regular
Range("C5").Select
ActiveCell.FormulaR1C1 = "'did you know"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=5, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=8, Length:=5).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = xlAutomatic
End With
End Sub

regards
FSt1
"dragonball20cal" wrote:


Hi I am have having problem applying bold format within string text. I
want to bold the Input!10 data pulled from another sheet. is there a
way to do this? is it even possible?

="your application will be renewing on"&" "&TEXT(Input!B10, "mmmm dd,
yyyy.")&" " & "In an effort to furnish you with the coverage that"



dan




--
dragonball20cal

You can set the **entire** text's font properties first and then go back
and change embedded parts of it afterward (instead of processing each part
separately)...

Sub differentformats()
'this bolds the word you and leave the other words regular
Range("C5").Select
ActiveCell.FormulaR1C1 = "'did you know"
With ActiveCell.Characters(Start:=1, Length:=Len(ActiveCell.Text)).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=5, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.ColorIndex = xlAutomatic
End With
End Sub


Rick



"FSt1" wrote in message
...
hi,
yes you can do that. in a regular text string. the code below show how
it's
done.
but you can't do that in a formula. reason being you have to have exact
start and stop points and exact lengths in order to have different formats
within the same cell and formulas can produce varing results. sorry.

Sub differentformats()
'this bolds the word you and leave the other words regular
Range("C5").Select
ActiveCell.FormulaR1C1 = "'did you know"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=5, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=8, Length:=5).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = xlAutomatic
End With
End Sub

regards
FSt1
"dragonball20cal" wrote:


Hi I am have having problem applying bold format within string text. I
want to bold the Input!10 data pulled from another sheet. is there a
way to do this? is it even possible?

="your application will be renewing on"&" "&TEXT(Input!B10, "mmmm dd,
yyyy.")&" " & "In an effort to furnish you with the coverage that"



dan




--
dragonball20cal

Rick and FSt1,

My interpretation is that the OP needs the code to update automatically
(ergo the formula) and the number of bolded characters needs to match the
length of the referenced date from cell B10 of sheet "Input". The date length
will of course vary (e.g. August vs May). Hard-coding the number of
characters won't suffice.

Greg

"Rick Rothstein (MVP - VB)" wrote:

You can set the **entire** text's font properties first and then go back
and change embedded parts of it afterward (instead of processing each part
separately)...

Sub differentformats()
'this bolds the word you and leave the other words regular
Range("C5").Select
ActiveCell.FormulaR1C1 = "'did you know"
With ActiveCell.Characters(Start:=1, Length:=Len(ActiveCell.Text)).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=5, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.ColorIndex = xlAutomatic
End With
End Sub


Rick



"FSt1" wrote in message
...
hi,
yes you can do that. in a regular text string. the code below show how
it's
done.
but you can't do that in a formula. reason being you have to have exact
start and stop points and exact lengths in order to have different formats
within the same cell and formulas can produce varing results. sorry.

Sub differentformats()
'this bolds the word you and leave the other words regular
Range("C5").Select
ActiveCell.FormulaR1C1 = "'did you know"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=5, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=8, Length:=5).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = xlAutomatic
End With
End Sub

regards
FSt1
"dragonball20cal" wrote:


Hi I am have having problem applying bold format within string text. I
want to bold the Input!10 data pulled from another sheet. is there a
way to do this? is it even possible?

="your application will be renewing on"&" "&TEXT(Input!B10, "mmmm dd,
yyyy.")&" " & "In an effort to furnish you with the coverage that"



dan




--
dragonball20cal

You can't format individual characters within a formula. The cell contents
need to be text and the change must be handled by code. The appended code
will update automatically and the number of characters bolded will match the
length of the referenced date.

In the "Input" sheet's code module, paste the following event code. To
access the sheet's code module, right click the sheet tab and select "View
Code". Change the sheet and cell reference from Sheet1.Range("D5") to suit.
Note that the number 38 in the code matches the start of the date in the text
string which follows this text:
"Your application will be renewing on "
If you change the above text, you must change this number accordingly.

Private Sub Worksheet_Calculate()
Dim c As Range
Dim t1 As String, t2 As String, t3 As String

Set c = Range("B10")
t1 = "Your application will be renewing on "
t2 = Format(c.Value, "mmmm dd yyyy")
t3 = ". In an effort to furnish you with the coverage that..."
With Application
.EnableEvents = False
With Sheets("Sheet1").Range("D5")
.Font.Bold = False
.Value = t1 & t2 & t3
.Characters(38, Len(t2)).Font.Bold = True
End With
.EnableEvents = True
End With
Set c = Nothing
End Sub

Regards,
Greg


"dragonball20cal" wrote:


Hi I am have having problem applying bold format within string text. I
want to bold the Input!10 data pulled from another sheet. is there a
way to do this? is it even possible?

="your application will be renewing on"&" "&TEXT(Input!B10, "mmmm dd,
yyyy.")&" " & "In an effort to furnish you with the coverage that"



dan




--
dragonball20cal

Alternatively, you can change the number 38 to:

Len(t1) + 1

Greg

"Greg Wilson" wrote:

You can't format individual characters within a formula. The cell contents
need to be text and the change must be handled by code. The appended code
will update automatically and the number of characters bolded will match the
length of the referenced date.

In the "Input" sheet's code module, paste the following event code. To
access the sheet's code module, right click the sheet tab and select "View
Code". Change the sheet and cell reference from Sheet1.Range("D5") to suit.
Note that the number 38 in the code matches the start of the date in the text
string which follows this text:
"Your application will be renewing on "
If you change the above text, you must change this number accordingly.

Private Sub Worksheet_Calculate()
Dim c As Range
Dim t1 As String, t2 As String, t3 As String

Set c = Range("B10")
t1 = "Your application will be renewing on "
t2 = Format(c.Value, "mmmm dd yyyy")
t3 = ". In an effort to furnish you with the coverage that..."
With Application
.EnableEvents = False
With Sheets("Sheet1").Range("D5")
.Font.Bold = False
.Value = t1 & t2 & t3
.Characters(38, Len(t2)).Font.Bold = True
End With
.EnableEvents = True
End With
Set c = Nothing
End Sub

Regards,
Greg


"dragonball20cal" wrote:


Hi I am have having problem applying bold format within string text. I
want to bold the Input!10 data pulled from another sheet. is there a
way to do this? is it even possible?

="your application will be renewing on"&" "&TEXT(Input!B10, "mmmm dd,
yyyy.")&" " & "In an effort to furnish you with the coverage that"



dan




--
dragonball20cal

Thanks all

I am new to Excel,

How do I go about putting these codes that you guys are mentioning. Any simple step by step process. Where do I insert the codes?

Thansk again

Dan

1. Right click the sheet tab of sheet "Input" (where the sheet name is).
This will display a popup toolbar that lets you select from a number of
options. Selet the "View Code" option. The sheet's code module should appear.
Then simply paste the appended (slightly updated) code.

2. Note that you will need to change the part where it references
Sheets("Sheet1").Range("D5")
to the appropriate sheet name and cell. You may also wish to tinker with the
code a bit.

3. You also need to enable macros for it to work. Ensure that High security
is not set. Set to either Medium or Low through: Tools Macro Security
Security Level tab.

4. Code follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim t1 As String, t2 As String, t3 As String

Set c = Range("B10")
t1 = "Your application will be renewing on "
t2 = Format(c.Value, "mmmm dd yyyy")
t3 = ". In an effort to furnish you with the coverage that..."
With Application
.EnableEvents = False
With Sheets("Sheet1").Range("D5")
.Font.Bold = False
.Value = t1 & t2 & t3
.Characters(Len(t1) + 1, Len(t2)).Font.Bold = True
End With
.EnableEvents = True
End With
Set c = Nothing
End Sub

Regards,
Greg