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("D1 0").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