changing font style in a complex worksheet function

I can't see why you are trying to set the value of A5 in this way. What you
seem to be saying is "I know I want it to say D10 so take the address of D10
which is $D$10 and use substitute to take out the $ signs." Why don't you
just type in D10 so your formula would look like this:

Range("A5").Value = "Usage Instruction: Vary contribution rate " _
& "D10 in order to set Actual Annuity D24 to the value of the " _
& "Required Annuity D19 by pressing the button above."

Substitue is a worksheet function so if you really wanted to use it you
would have to do it something like this:

application.WorksheetFunction.Substitute(range("D10").Address,"$","")

PS you are also missing a couple of End With's so you whole event should be:

Private Sub Worksheet_Calculate()
On Error GoTo ErrorHandler
Application.EnableEvents = False

Range("A5").Value = "Usage Instruction: Vary contribution rate " _
& "D10 in order to set Actual Annuity D24 to the value of the " _
& "Required Annuity D19 by pressing the button above."

' The following code changes the style of the words CONTRIBUTION RATE in the
'first phrase in A5 to bold and green
With Range("A5").Characters(Start:=25, Length:=17).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With

' The following code attempts to change the style of the words ACTUAL
'ANNUITY in the second phrase in A5 to bold and green
With Range("A5").Characters(Start:=63, Length:=14).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With

' The following code attempts to change the style of the words REQUIRED
'ANNUITY in the third phrase in A5 to bold and green
With Range("A5").Characters(Start:=102, Length:=16).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With
ErrorHandler:
Application.EnableEvents = True
End Sub

Hope this helps
Rowan



"gvm" wrote:

> I have previously had help to change the font style of a fairly simple
> worksheet function. Now I want to do similarin a worksheet function that has
> four character strings interspersed with functions. The code I use follows
> and the first problem I get is a compile error: there is a problem with the
> CELL function. Thanks again in anticipation, I appreciate the support of this
> community so much, it's excellent ... Greg
>
> Private Sub Worksheet_Calculate()
> Application.EnableEvents = False
> Range("A5").Value = "Usage Instruction: Vary contribution rate (cell " &
> Substitute(CELL("address", D10), "$", "") & ") in order to set Actual Annuity
> (" & Substitute(CELL("address", D24), "$", "") & ") to the value of the
> Required Annuity (" & Substitute(CELL("address", D19), "$", "") & ") by
> pressing the button above."
>
> ' The following code changes the style of the words CONTRIBUTION RATE in the
> first phrase in A5 to bold and green
> With Range("A5").Characters(Start:=25, Length:=17).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
>
> ' The following code attempts to change the style of the words ACTUAL
> ANNUITY in the second phrase in A5 to bold and green
> With Range("A5").Characters(Start:=68, Length:=14).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
>
> ' The following code attempts to change the style of the words REQUIRED
> ANNUITY in the third phrase in A5 to bold and green
> With Range("A5").Characters(Start:=106, Length:=16).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
> End With
> Application.EnableEvents = True
> End Sub

No Rowan, the reference to D10 is dynamic so will change if changes occur in
the spreadsheet. What I really need help with is the formatting of the text

"Rowan" wrote:

> I can't see why you are trying to set the value of A5 in this way. What you
> seem to be saying is "I know I want it to say D10 so take the address of D10
> which is $D$10 and use substitute to take out the $ signs." Why don't you
> just type in D10 so your formula would look like this:
>
> Range("A5").Value = "Usage Instruction: Vary contribution rate " _
> & "D10 in order to set Actual Annuity D24 to the value of the " _
> & "Required Annuity D19 by pressing the button above."
>
> Substitue is a worksheet function so if you really wanted to use it you
> would have to do it something like this:
>
> application.WorksheetFunction.Substitute(range("D10").Address,"$","")
>
> PS you are also missing a couple of End With's so you whole event should be:
>
> Private Sub Worksheet_Calculate()
> On Error GoTo ErrorHandler
> Application.EnableEvents = False
>
> Range("A5").Value = "Usage Instruction: Vary contribution rate " _
> & "D10 in order to set Actual Annuity D24 to the value of the " _
> & "Required Annuity D19 by pressing the button above."
>
> ' The following code changes the style of the words CONTRIBUTION RATE in the
> 'first phrase in A5 to bold and green
> With Range("A5").Characters(Start:=25, Length:=17).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
> End With
>
> ' The following code attempts to change the style of the words ACTUAL
> 'ANNUITY in the second phrase in A5 to bold and green
> With Range("A5").Characters(Start:=63, Length:=14).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
> End With
>
> ' The following code attempts to change the style of the words REQUIRED
> 'ANNUITY in the third phrase in A5 to bold and green
> With Range("A5").Characters(Start:=102, Length:=16).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
> End With
> ErrorHandler:
> Application.EnableEvents = True
> End Sub
>
> Hope this helps
> Rowan
>
>
>
> "gvm" wrote:
>
> > I have previously had help to change the font style of a fairly simple
> > worksheet function. Now I want to do similarin a worksheet function that has
> > four character strings interspersed with functions. The code I use follows
> > and the first problem I get is a compile error: there is a problem with the
> > CELL function. Thanks again in anticipation, I appreciate the support of this
> > community so much, it's excellent ... Greg
> >
> > Private Sub Worksheet_Calculate()
> > Application.EnableEvents = False
> > Range("A5").Value = "Usage Instruction: Vary contribution rate (cell " &
> > Substitute(CELL("address", D10), "$", "") & ") in order to set Actual Annuity
> > (" & Substitute(CELL("address", D24), "$", "") & ") to the value of the
> > Required Annuity (" & Substitute(CELL("address", D19), "$", "") & ") by
> > pressing the button above."
> >
> > ' The following code changes the style of the words CONTRIBUTION RATE in the
> > first phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=25, Length:=17).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> >
> > ' The following code attempts to change the style of the words ACTUAL
> > ANNUITY in the second phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=68, Length:=14).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> >
> > ' The following code attempts to change the style of the words REQUIRED
> > ANNUITY in the third phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=106, Length:=16).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> > End With
> > Application.EnableEvents = True
> > End Sub

OK I see where you are coming from now. The easiest way would be to insert
named ranges for the three cells, I have called them ContRate, ActAn and
ReqAn. Then you can set the value of A5 this way:

Range("A5").Value = "Usage Instruction: Vary contribution rate " _
& Application.WorksheetFunction. _
Substitute(Range("ContRate").Address, "$", "") _
& " in order to set Actual Annuity " _
& Application.WorksheetFunction. _
Substitute(Range("ActAn").Address, "$", "") _
& " to the value of the Required Annuity " _
& Application.WorksheetFunction. _
Substitute(Range("ReqAn").Address, "$", "") _
& " by pressing the button above."

This way you will get the right cell even if rows are added etc.

Hope this helps
Rowan

"gvm" wrote:

> No Rowan, the reference to D10 is dynamic so will change if changes occur in
> the spreadsheet. What I really need help with is the formatting of the text
>
> "Rowan" wrote:
>
> > I can't see why you are trying to set the value of A5 in this way. What you
> > seem to be saying is "I know I want it to say D10 so take the address of D10
> > which is $D$10 and use substitute to take out the $ signs." Why don't you
> > just type in D10 so your formula would look like this:
> >
> > Range("A5").Value = "Usage Instruction: Vary contribution rate " _
> > & "D10 in order to set Actual Annuity D24 to the value of the " _
> > & "Required Annuity D19 by pressing the button above."
> >
> > Substitue is a worksheet function so if you really wanted to use it you
> > would have to do it something like this:
> >
> > application.WorksheetFunction.Substitute(range("D10").Address,"$","")
> >
> > PS you are also missing a couple of End With's so you whole event should be:
> >
> > Private Sub Worksheet_Calculate()
> > On Error GoTo ErrorHandler
> > Application.EnableEvents = False
> >
> > Range("A5").Value = "Usage Instruction: Vary contribution rate " _
> > & "D10 in order to set Actual Annuity D24 to the value of the " _
> > & "Required Annuity D19 by pressing the button above."
> >
> > ' The following code changes the style of the words CONTRIBUTION RATE in the
> > 'first phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=25, Length:=17).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> > End With
> >
> > ' The following code attempts to change the style of the words ACTUAL
> > 'ANNUITY in the second phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=63, Length:=14).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> > End With
> >
> > ' The following code attempts to change the style of the words REQUIRED
> > 'ANNUITY in the third phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=102, Length:=16).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> > End With
> > ErrorHandler:
> > Application.EnableEvents = True
> > End Sub
> >
> > Hope this helps
> > Rowan
> >
> >
> >
> > "gvm" wrote:
> >
> > > I have previously had help to change the font style of a fairly simple
> > > worksheet function. Now I want to do similarin a worksheet function that has
> > > four character strings interspersed with functions. The code I use follows
> > > and the first problem I get is a compile error: there is a problem with the
> > > CELL function. Thanks again in anticipation, I appreciate the support of this
> > > community so much, it's excellent ... Greg
> > >
> > > Private Sub Worksheet_Calculate()
> > > Application.EnableEvents = False
> > > Range("A5").Value = "Usage Instruction: Vary contribution rate (cell " &
> > > Substitute(CELL("address", D10), "$", "") & ") in order to set Actual Annuity
> > > (" & Substitute(CELL("address", D24), "$", "") & ") to the value of the
> > > Required Annuity (" & Substitute(CELL("address", D19), "$", "") & ") by
> > > pressing the button above."
> > >
> > > ' The following code changes the style of the words CONTRIBUTION RATE in the
> > > first phrase in A5 to bold and green
> > > With Range("A5").Characters(Start:=25, Length:=17).Font
> > > .FontStyle = "Bold"
> > > .ColorIndex = 50
> > >
> > > ' The following code attempts to change the style of the words ACTUAL
> > > ANNUITY in the second phrase in A5 to bold and green
> > > With Range("A5").Characters(Start:=68, Length:=14).Font
> > > .FontStyle = "Bold"
> > > .ColorIndex = 50
> > >
> > > ' The following code attempts to change the style of the words REQUIRED
> > > ANNUITY in the third phrase in A5 to bold and green
> > > With Range("A5").Characters(Start:=106, Length:=16).Font
> > > .FontStyle = "Bold"
> > > .ColorIndex = 50
> > > End With
> > > Application.EnableEvents = True
> > > End Sub

That helps Rowan thanks, but now how do I change the font style of the terms
"contribution rate", "actual annuity" and "required annuity" in the various
character strings to bold and green?

That bit you were doing fine except for a couple of missing End With's. The
only thing I would add there is a check on the length of the first two cell
references as this will affect which characters you need to format:

Private Sub Worksheet_Calculate()
On Error GoTo ErrorHandler
Application.EnableEvents = False

Dim LCRate As Integer
Dim LActA As Integer

Range("A5").Value = "Usage Instruction: Vary contribution rate " _
& Application.WorksheetFunction. _
Substitute(Range("ContRate").Address, "$", "") _
& " in order to set Actual Annuity " _
& Application.WorksheetFunction. _
Substitute(Range("ActAn").Address, "$", "") _
& " to the value of the Required Annuity " _
& Application.WorksheetFunction. _
Substitute(Range("ReqAn").Address, "$", "") _
& " by pressing the button above."

LCRate = Len(Application.WorksheetFunction. _
Substitute(Range("ContRate").Address, "$", ""))
LActA = Len(Application.WorksheetFunction. _
Substitute(Range("ActAn").Address, "$", ""))

' The following code changes the style of the words CONTRIBUTION RATE
'in the first phrase in A5 to bold and green
With Range("A5").Characters(Start:=25, Length:=17).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With

' The following code attempts to change the style of the words ACTUAL
'ANNUITY in the second phrase in A5 to bold and green
With Range("A5").Characters(Start:=60 + LCRate, Length:=14).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With

' The following code attempts to change the style of the words REQUIRED
'ANNUITY in the third phrase in A5 to bold and green
With Range("A5").Characters(Start:=96 + LCRate + LActA, Length:=16).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With

ErrorHandler:
Application.EnableEvents = True
End Sub

Regards
Rowan

"gvm" wrote:

> That helps Rowan thanks, but now how do I change the font style of the terms
> "contribution rate", "actual annuity" and "required annuity" in the various
> character strings to bold and green?
>

That's perfect Rowan, thankyou heaps

That's perfect Rowan, thankyou heaps

That bit you were doing fine except for a couple of missing End With's. The
only thing I would add there is a check on the length of the first two cell
references as this will affect which characters you need to format:

Private Sub Worksheet_Calculate()
On Error GoTo ErrorHandler
Application.EnableEvents = False

Dim LCRate As Integer
Dim LActA As Integer

Range("A5").Value = "Usage Instruction: Vary contribution rate " _
& Application.WorksheetFunction. _
Substitute(Range("ContRate").Address, "$", "") _
& " in order to set Actual Annuity " _
& Application.WorksheetFunction. _
Substitute(Range("ActAn").Address, "$", "") _
& " to the value of the Required Annuity " _
& Application.WorksheetFunction. _
Substitute(Range("ReqAn").Address, "$", "") _
& " by pressing the button above."

LCRate = Len(Application.WorksheetFunction. _
Substitute(Range("ContRate").Address, "$", ""))
LActA = Len(Application.WorksheetFunction. _
Substitute(Range("ActAn").Address, "$", ""))

' The following code changes the style of the words CONTRIBUTION RATE
'in the first phrase in A5 to bold and green
With Range("A5").Characters(Start:=25, Length:=17).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With

' The following code attempts to change the style of the words ACTUAL
'ANNUITY in the second phrase in A5 to bold and green
With Range("A5").Characters(Start:=60 + LCRate, Length:=14).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With

' The following code attempts to change the style of the words REQUIRED
'ANNUITY in the third phrase in A5 to bold and green
With Range("A5").Characters(Start:=96 + LCRate + LActA, Length:=16).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With

ErrorHandler:
Application.EnableEvents = True
End Sub

Regards
Rowan

"gvm" wrote:

> That helps Rowan thanks, but now how do I change the font style of the terms
> "contribution rate", "actual annuity" and "required annuity" in the various
> character strings to bold and green?
>

That helps Rowan thanks, but now how do I change the font style of the terms
"contribution rate", "actual annuity" and "required annuity" in the various
character strings to bold and green?

OK I see where you are coming from now. The easiest way would be to insert
named ranges for the three cells, I have called them ContRate, ActAn and
ReqAn. Then you can set the value of A5 this way:

Range("A5").Value = "Usage Instruction: Vary contribution rate " _
& Application.WorksheetFunction. _
Substitute(Range("ContRate").Address, "$", "") _
& " in order to set Actual Annuity " _
& Application.WorksheetFunction. _
Substitute(Range("ActAn").Address, "$", "") _
& " to the value of the Required Annuity " _
& Application.WorksheetFunction. _
Substitute(Range("ReqAn").Address, "$", "") _
& " by pressing the button above."

This way you will get the right cell even if rows are added etc.

Hope this helps
Rowan

"gvm" wrote:

> No Rowan, the reference to D10 is dynamic so will change if changes occur in
> the spreadsheet. What I really need help with is the formatting of the text
>
> "Rowan" wrote:
>
> > I can't see why you are trying to set the value of A5 in this way. What you
> > seem to be saying is "I know I want it to say D10 so take the address of D10
> > which is $D$10 and use substitute to take out the $ signs." Why don't you
> > just type in D10 so your formula would look like this:
> >
> > Range("A5").Value = "Usage Instruction: Vary contribution rate " _
> > & "D10 in order to set Actual Annuity D24 to the value of the " _
> > & "Required Annuity D19 by pressing the button above."
> >
> > Substitue is a worksheet function so if you really wanted to use it you
> > would have to do it something like this:
> >
> > application.WorksheetFunction.Substitute(range("D10").Address,"$","")
> >
> > PS you are also missing a couple of End With's so you whole event should be:
> >
> > Private Sub Worksheet_Calculate()
> > On Error GoTo ErrorHandler
> > Application.EnableEvents = False
> >
> > Range("A5").Value = "Usage Instruction: Vary contribution rate " _
> > & "D10 in order to set Actual Annuity D24 to the value of the " _
> > & "Required Annuity D19 by pressing the button above."
> >
> > ' The following code changes the style of the words CONTRIBUTION RATE in the
> > 'first phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=25, Length:=17).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> > End With
> >
> > ' The following code attempts to change the style of the words ACTUAL
> > 'ANNUITY in the second phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=63, Length:=14).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> > End With
> >
> > ' The following code attempts to change the style of the words REQUIRED
> > 'ANNUITY in the third phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=102, Length:=16).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> > End With
> > ErrorHandler:
> > Application.EnableEvents = True
> > End Sub
> >
> > Hope this helps
> > Rowan
> >
> >
> >
> > "gvm" wrote:
> >
> > > I have previously had help to change the font style of a fairly simple
> > > worksheet function. Now I want to do similarin a worksheet function that has
> > > four character strings interspersed with functions. The code I use follows
> > > and the first problem I get is a compile error: there is a problem with the
> > > CELL function. Thanks again in anticipation, I appreciate the support of this
> > > community so much, it's excellent ... Greg
> > >
> > > Private Sub Worksheet_Calculate()
> > > Application.EnableEvents = False
> > > Range("A5").Value = "Usage Instruction: Vary contribution rate (cell " &
> > > Substitute(CELL("address", D10), "$", "") & ") in order to set Actual Annuity
> > > (" & Substitute(CELL("address", D24), "$", "") & ") to the value of the
> > > Required Annuity (" & Substitute(CELL("address", D19), "$", "") & ") by
> > > pressing the button above."
> > >
> > > ' The following code changes the style of the words CONTRIBUTION RATE in the
> > > first phrase in A5 to bold and green
> > > With Range("A5").Characters(Start:=25, Length:=17).Font
> > > .FontStyle = "Bold"
> > > .ColorIndex = 50
> > >
> > > ' The following code attempts to change the style of the words ACTUAL
> > > ANNUITY in the second phrase in A5 to bold and green
> > > With Range("A5").Characters(Start:=68, Length:=14).Font
> > > .FontStyle = "Bold"
> > > .ColorIndex = 50
> > >
> > > ' The following code attempts to change the style of the words REQUIRED
> > > ANNUITY in the third phrase in A5 to bold and green
> > > With Range("A5").Characters(Start:=106, Length:=16).Font
> > > .FontStyle = "Bold"
> > > .ColorIndex = 50
> > > End With
> > > Application.EnableEvents = True
> > > End Sub

No Rowan, the reference to D10 is dynamic so will change if changes occur in
the spreadsheet. What I really need help with is the formatting of the text

"Rowan" wrote:

> I can't see why you are trying to set the value of A5 in this way. What you
> seem to be saying is "I know I want it to say D10 so take the address of D10
> which is $D$10 and use substitute to take out the $ signs." Why don't you
> just type in D10 so your formula would look like this:
>
> Range("A5").Value = "Usage Instruction: Vary contribution rate " _
> & "D10 in order to set Actual Annuity D24 to the value of the " _
> & "Required Annuity D19 by pressing the button above."
>
> Substitue is a worksheet function so if you really wanted to use it you
> would have to do it something like this:
>
> application.WorksheetFunction.Substitute(range("D10").Address,"$","")
>
> PS you are also missing a couple of End With's so you whole event should be:
>
> Private Sub Worksheet_Calculate()
> On Error GoTo ErrorHandler
> Application.EnableEvents = False
>
> Range("A5").Value = "Usage Instruction: Vary contribution rate " _
> & "D10 in order to set Actual Annuity D24 to the value of the " _
> & "Required Annuity D19 by pressing the button above."
>
> ' The following code changes the style of the words CONTRIBUTION RATE in the
> 'first phrase in A5 to bold and green
> With Range("A5").Characters(Start:=25, Length:=17).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
> End With
>
> ' The following code attempts to change the style of the words ACTUAL
> 'ANNUITY in the second phrase in A5 to bold and green
> With Range("A5").Characters(Start:=63, Length:=14).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
> End With
>
> ' The following code attempts to change the style of the words REQUIRED
> 'ANNUITY in the third phrase in A5 to bold and green
> With Range("A5").Characters(Start:=102, Length:=16).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
> End With
> ErrorHandler:
> Application.EnableEvents = True
> End Sub
>
> Hope this helps
> Rowan
>
>
>
> "gvm" wrote:
>
> > I have previously had help to change the font style of a fairly simple
> > worksheet function. Now I want to do similarin a worksheet function that has
> > four character strings interspersed with functions. The code I use follows
> > and the first problem I get is a compile error: there is a problem with the
> > CELL function. Thanks again in anticipation, I appreciate the support of this
> > community so much, it's excellent ... Greg
> >
> > Private Sub Worksheet_Calculate()
> > Application.EnableEvents = False
> > Range("A5").Value = "Usage Instruction: Vary contribution rate (cell " &
> > Substitute(CELL("address", D10), "$", "") & ") in order to set Actual Annuity
> > (" & Substitute(CELL("address", D24), "$", "") & ") to the value of the
> > Required Annuity (" & Substitute(CELL("address", D19), "$", "") & ") by
> > pressing the button above."
> >
> > ' The following code changes the style of the words CONTRIBUTION RATE in the
> > first phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=25, Length:=17).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> >
> > ' The following code attempts to change the style of the words ACTUAL
> > ANNUITY in the second phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=68, Length:=14).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> >
> > ' The following code attempts to change the style of the words REQUIRED
> > ANNUITY in the third phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=106, Length:=16).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> > End With
> > Application.EnableEvents = True
> > End Sub

I can't see why you are trying to set the value of A5 in this way. What you
seem to be saying is "I know I want it to say D10 so take the address of D10
which is $D$10 and use substitute to take out the $ signs." Why don't you
just type in D10 so your formula would look like this:

Range("A5").Value = "Usage Instruction: Vary contribution rate " _
& "D10 in order to set Actual Annuity D24 to the value of the " _
& "Required Annuity D19 by pressing the button above."

Substitue is a worksheet function so if you really wanted to use it you
would have to do it something like this:

application.WorksheetFunction.Substitute(range("D10").Address,"$","")

PS you are also missing a couple of End With's so you whole event should be:

Private Sub Worksheet_Calculate()
On Error GoTo ErrorHandler
Application.EnableEvents = False

Range("A5").Value = "Usage Instruction: Vary contribution rate " _
& "D10 in order to set Actual Annuity D24 to the value of the " _
& "Required Annuity D19 by pressing the button above."

' The following code changes the style of the words CONTRIBUTION RATE in the
'first phrase in A5 to bold and green
With Range("A5").Characters(Start:=25, Length:=17).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With

' The following code attempts to change the style of the words ACTUAL
'ANNUITY in the second phrase in A5 to bold and green
With Range("A5").Characters(Start:=63, Length:=14).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With

' The following code attempts to change the style of the words REQUIRED
'ANNUITY in the third phrase in A5 to bold and green
With Range("A5").Characters(Start:=102, Length:=16).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With
ErrorHandler:
Application.EnableEvents = True
End Sub

Hope this helps
Rowan



"gvm" wrote:

> I have previously had help to change the font style of a fairly simple
> worksheet function. Now I want to do similarin a worksheet function that has
> four character strings interspersed with functions. The code I use follows
> and the first problem I get is a compile error: there is a problem with the
> CELL function. Thanks again in anticipation, I appreciate the support of this
> community so much, it's excellent ... Greg
>
> Private Sub Worksheet_Calculate()
> Application.EnableEvents = False
> Range("A5").Value = "Usage Instruction: Vary contribution rate (cell " &
> Substitute(CELL("address", D10), "$", "") & ") in order to set Actual Annuity
> (" & Substitute(CELL("address", D24), "$", "") & ") to the value of the
> Required Annuity (" & Substitute(CELL("address", D19), "$", "") & ") by
> pressing the button above."
>
> ' The following code changes the style of the words CONTRIBUTION RATE in the
> first phrase in A5 to bold and green
> With Range("A5").Characters(Start:=25, Length:=17).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
>
> ' The following code attempts to change the style of the words ACTUAL
> ANNUITY in the second phrase in A5 to bold and green
> With Range("A5").Characters(Start:=68, Length:=14).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
>
> ' The following code attempts to change the style of the words REQUIRED
> ANNUITY in the third phrase in A5 to bold and green
> With Range("A5").Characters(Start:=106, Length:=16).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
> End With
> Application.EnableEvents = True
> End Sub

That's perfect Rowan, thankyou heaps

That bit you were doing fine except for a couple of missing End With's. The
only thing I would add there is a check on the length of the first two cell
references as this will affect which characters you need to format:

Private Sub Worksheet_Calculate()
On Error GoTo ErrorHandler
Application.EnableEvents = False

Dim LCRate As Integer
Dim LActA As Integer

Range("A5").Value = "Usage Instruction: Vary contribution rate " _
& Application.WorksheetFunction. _
Substitute(Range("ContRate").Address, "$", "") _
& " in order to set Actual Annuity " _
& Application.WorksheetFunction. _
Substitute(Range("ActAn").Address, "$", "") _
& " to the value of the Required Annuity " _
& Application.WorksheetFunction. _
Substitute(Range("ReqAn").Address, "$", "") _
& " by pressing the button above."

LCRate = Len(Application.WorksheetFunction. _
Substitute(Range("ContRate").Address, "$", ""))
LActA = Len(Application.WorksheetFunction. _
Substitute(Range("ActAn").Address, "$", ""))

' The following code changes the style of the words CONTRIBUTION RATE
'in the first phrase in A5 to bold and green
With Range("A5").Characters(Start:=25, Length:=17).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With

' The following code attempts to change the style of the words ACTUAL
'ANNUITY in the second phrase in A5 to bold and green
With Range("A5").Characters(Start:=60 + LCRate, Length:=14).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With

' The following code attempts to change the style of the words REQUIRED
'ANNUITY in the third phrase in A5 to bold and green
With Range("A5").Characters(Start:=96 + LCRate + LActA, Length:=16).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With

ErrorHandler:
Application.EnableEvents = True
End Sub

Regards
Rowan

"gvm" wrote:

> That helps Rowan thanks, but now how do I change the font style of the terms
> "contribution rate", "actual annuity" and "required annuity" in the various
> character strings to bold and green?
>

That helps Rowan thanks, but now how do I change the font style of the terms
"contribution rate", "actual annuity" and "required annuity" in the various
character strings to bold and green?

OK I see where you are coming from now. The easiest way would be to insert
named ranges for the three cells, I have called them ContRate, ActAn and
ReqAn. Then you can set the value of A5 this way:

Range("A5").Value = "Usage Instruction: Vary contribution rate " _
& Application.WorksheetFunction. _
Substitute(Range("ContRate").Address, "$", "") _
& " in order to set Actual Annuity " _
& Application.WorksheetFunction. _
Substitute(Range("ActAn").Address, "$", "") _
& " to the value of the Required Annuity " _
& Application.WorksheetFunction. _
Substitute(Range("ReqAn").Address, "$", "") _
& " by pressing the button above."

This way you will get the right cell even if rows are added etc.

Hope this helps
Rowan

"gvm" wrote:

> No Rowan, the reference to D10 is dynamic so will change if changes occur in
> the spreadsheet. What I really need help with is the formatting of the text
>
> "Rowan" wrote:
>
> > I can't see why you are trying to set the value of A5 in this way. What you
> > seem to be saying is "I know I want it to say D10 so take the address of D10
> > which is $D$10 and use substitute to take out the $ signs." Why don't you
> > just type in D10 so your formula would look like this:
> >
> > Range("A5").Value = "Usage Instruction: Vary contribution rate " _
> > & "D10 in order to set Actual Annuity D24 to the value of the " _
> > & "Required Annuity D19 by pressing the button above."
> >
> > Substitue is a worksheet function so if you really wanted to use it you
> > would have to do it something like this:
> >
> > application.WorksheetFunction.Substitute(range("D10").Address,"$","")
> >
> > PS you are also missing a couple of End With's so you whole event should be:
> >
> > Private Sub Worksheet_Calculate()
> > On Error GoTo ErrorHandler
> > Application.EnableEvents = False
> >
> > Range("A5").Value = "Usage Instruction: Vary contribution rate " _
> > & "D10 in order to set Actual Annuity D24 to the value of the " _
> > & "Required Annuity D19 by pressing the button above."
> >
> > ' The following code changes the style of the words CONTRIBUTION RATE in the
> > 'first phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=25, Length:=17).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> > End With
> >
> > ' The following code attempts to change the style of the words ACTUAL
> > 'ANNUITY in the second phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=63, Length:=14).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> > End With
> >
> > ' The following code attempts to change the style of the words REQUIRED
> > 'ANNUITY in the third phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=102, Length:=16).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> > End With
> > ErrorHandler:
> > Application.EnableEvents = True
> > End Sub
> >
> > Hope this helps
> > Rowan
> >
> >
> >
> > "gvm" wrote:
> >
> > > I have previously had help to change the font style of a fairly simple
> > > worksheet function. Now I want to do similarin a worksheet function that has
> > > four character strings interspersed with functions. The code I use follows
> > > and the first problem I get is a compile error: there is a problem with the
> > > CELL function. Thanks again in anticipation, I appreciate the support of this
> > > community so much, it's excellent ... Greg
> > >
> > > Private Sub Worksheet_Calculate()
> > > Application.EnableEvents = False
> > > Range("A5").Value = "Usage Instruction: Vary contribution rate (cell " &
> > > Substitute(CELL("address", D10), "$", "") & ") in order to set Actual Annuity
> > > (" & Substitute(CELL("address", D24), "$", "") & ") to the value of the
> > > Required Annuity (" & Substitute(CELL("address", D19), "$", "") & ") by
> > > pressing the button above."
> > >
> > > ' The following code changes the style of the words CONTRIBUTION RATE in the
> > > first phrase in A5 to bold and green
> > > With Range("A5").Characters(Start:=25, Length:=17).Font
> > > .FontStyle = "Bold"
> > > .ColorIndex = 50
> > >
> > > ' The following code attempts to change the style of the words ACTUAL
> > > ANNUITY in the second phrase in A5 to bold and green
> > > With Range("A5").Characters(Start:=68, Length:=14).Font
> > > .FontStyle = "Bold"
> > > .ColorIndex = 50
> > >
> > > ' The following code attempts to change the style of the words REQUIRED
> > > ANNUITY in the third phrase in A5 to bold and green
> > > With Range("A5").Characters(Start:=106, Length:=16).Font
> > > .FontStyle = "Bold"
> > > .ColorIndex = 50
> > > End With
> > > Application.EnableEvents = True
> > > End Sub

No Rowan, the reference to D10 is dynamic so will change if changes occur in
the spreadsheet. What I really need help with is the formatting of the text

"Rowan" wrote:

> I can't see why you are trying to set the value of A5 in this way. What you
> seem to be saying is "I know I want it to say D10 so take the address of D10
> which is $D$10 and use substitute to take out the $ signs." Why don't you
> just type in D10 so your formula would look like this:
>
> Range("A5").Value = "Usage Instruction: Vary contribution rate " _
> & "D10 in order to set Actual Annuity D24 to the value of the " _
> & "Required Annuity D19 by pressing the button above."
>
> Substitue is a worksheet function so if you really wanted to use it you
> would have to do it something like this:
>
> application.WorksheetFunction.Substitute(range("D10").Address,"$","")
>
> PS you are also missing a couple of End With's so you whole event should be:
>
> Private Sub Worksheet_Calculate()
> On Error GoTo ErrorHandler
> Application.EnableEvents = False
>
> Range("A5").Value = "Usage Instruction: Vary contribution rate " _
> & "D10 in order to set Actual Annuity D24 to the value of the " _
> & "Required Annuity D19 by pressing the button above."
>
> ' The following code changes the style of the words CONTRIBUTION RATE in the
> 'first phrase in A5 to bold and green
> With Range("A5").Characters(Start:=25, Length:=17).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
> End With
>
> ' The following code attempts to change the style of the words ACTUAL
> 'ANNUITY in the second phrase in A5 to bold and green
> With Range("A5").Characters(Start:=63, Length:=14).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
> End With
>
> ' The following code attempts to change the style of the words REQUIRED
> 'ANNUITY in the third phrase in A5 to bold and green
> With Range("A5").Characters(Start:=102, Length:=16).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
> End With
> ErrorHandler:
> Application.EnableEvents = True
> End Sub
>
> Hope this helps
> Rowan
>
>
>
> "gvm" wrote:
>
> > I have previously had help to change the font style of a fairly simple
> > worksheet function. Now I want to do similarin a worksheet function that has
> > four character strings interspersed with functions. The code I use follows
> > and the first problem I get is a compile error: there is a problem with the
> > CELL function. Thanks again in anticipation, I appreciate the support of this
> > community so much, it's excellent ... Greg
> >
> > Private Sub Worksheet_Calculate()
> > Application.EnableEvents = False
> > Range("A5").Value = "Usage Instruction: Vary contribution rate (cell " &
> > Substitute(CELL("address", D10), "$", "") & ") in order to set Actual Annuity
> > (" & Substitute(CELL("address", D24), "$", "") & ") to the value of the
> > Required Annuity (" & Substitute(CELL("address", D19), "$", "") & ") by
> > pressing the button above."
> >
> > ' The following code changes the style of the words CONTRIBUTION RATE in the
> > first phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=25, Length:=17).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> >
> > ' The following code attempts to change the style of the words ACTUAL
> > ANNUITY in the second phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=68, Length:=14).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> >
> > ' The following code attempts to change the style of the words REQUIRED
> > ANNUITY in the third phrase in A5 to bold and green
> > With Range("A5").Characters(Start:=106, Length:=16).Font
> > .FontStyle = "Bold"
> > .ColorIndex = 50
> > End With
> > Application.EnableEvents = True
> > End Sub

I can't see why you are trying to set the value of A5 in this way. What you
seem to be saying is "I know I want it to say D10 so take the address of D10
which is $D$10 and use substitute to take out the $ signs." Why don't you
just type in D10 so your formula would look like this:

Range("A5").Value = "Usage Instruction: Vary contribution rate " _
& "D10 in order to set Actual Annuity D24 to the value of the " _
& "Required Annuity D19 by pressing the button above."

Substitue is a worksheet function so if you really wanted to use it you
would have to do it something like this:

application.WorksheetFunction.Substitute(range("D10").Address,"$","")

PS you are also missing a couple of End With's so you whole event should be:

Private Sub Worksheet_Calculate()
On Error GoTo ErrorHandler
Application.EnableEvents = False

Range("A5").Value = "Usage Instruction: Vary contribution rate " _
& "D10 in order to set Actual Annuity D24 to the value of the " _
& "Required Annuity D19 by pressing the button above."

' The following code changes the style of the words CONTRIBUTION RATE in the
'first phrase in A5 to bold and green
With Range("A5").Characters(Start:=25, Length:=17).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With

' The following code attempts to change the style of the words ACTUAL
'ANNUITY in the second phrase in A5 to bold and green
With Range("A5").Characters(Start:=63, Length:=14).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With

' The following code attempts to change the style of the words REQUIRED
'ANNUITY in the third phrase in A5 to bold and green
With Range("A5").Characters(Start:=102, Length:=16).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With
ErrorHandler:
Application.EnableEvents = True
End Sub

Hope this helps
Rowan



"gvm" wrote:

> I have previously had help to change the font style of a fairly simple
> worksheet function. Now I want to do similarin a worksheet function that has
> four character strings interspersed with functions. The code I use follows
> and the first problem I get is a compile error: there is a problem with the
> CELL function. Thanks again in anticipation, I appreciate the support of this
> community so much, it's excellent ... Greg
>
> Private Sub Worksheet_Calculate()
> Application.EnableEvents = False
> Range("A5").Value = "Usage Instruction: Vary contribution rate (cell " &
> Substitute(CELL("address", D10), "$", "") & ") in order to set Actual Annuity
> (" & Substitute(CELL("address", D24), "$", "") & ") to the value of the
> Required Annuity (" & Substitute(CELL("address", D19), "$", "") & ") by
> pressing the button above."
>
> ' The following code changes the style of the words CONTRIBUTION RATE in the
> first phrase in A5 to bold and green
> With Range("A5").Characters(Start:=25, Length:=17).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
>
> ' The following code attempts to change the style of the words ACTUAL
> ANNUITY in the second phrase in A5 to bold and green
> With Range("A5").Characters(Start:=68, Length:=14).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
>
> ' The following code attempts to change the style of the words REQUIRED
> ANNUITY in the third phrase in A5 to bold and green
> With Range("A5").Characters(Start:=106, Length:=16).Font
> .FontStyle = "Bold"
> .ColorIndex = 50
> End With
> Application.EnableEvents = True
> End Sub

I have previously had help to change the font style of a fairly simple
worksheet function. Now I want to do similarin a worksheet function that has
four character strings interspersed with functions. The code I use follows
and the first problem I get is a compile error: there is a problem with the
CELL function. Thanks again in anticipation, I appreciate the support of this
community so much, it's excellent ... Greg

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("A5").Value = "Usage Instruction: Vary contribution rate (cell " &
Substitute(CELL("address", D10), "$", "") & ") in order to set Actual Annuity
(" & Substitute(CELL("address", D24), "$", "") & ") to the value of the
Required Annuity (" & Substitute(CELL("address", D19), "$", "") & ") by
pressing the button above."

' The following code changes the style of the words CONTRIBUTION RATE in the
first phrase in A5 to bold and green
With Range("A5").Characters(Start:=25, Length:=17).Font
.FontStyle = "Bold"
.ColorIndex = 50

' The following code attempts to change the style of the words ACTUAL
ANNUITY in the second phrase in A5 to bold and green
With Range("A5").Characters(Start:=68, Length:=14).Font
.FontStyle = "Bold"
.ColorIndex = 50

' The following code attempts to change the style of the words REQUIRED
ANNUITY in the third phrase in A5 to bold and green
With Range("A5").Characters(Start:=106, Length:=16).Font
.FontStyle = "Bold"
.ColorIndex = 50
End With
Application.EnableEvents = True
End Sub