formatting text strings

[jerrym]

I am embedding a reference to a cell within a text string. The content
of the referenced cell can be a positive or negative number. I want th
number to print blue if positive, red if negative. The actual tex
string is as follows:

="By the end of the planning horizon (December 31, "&SETUP!C23&"), yo
can expect to have accumulated "&T13&" of approximatel
"&TEXT($S$13,"[Blue]$#,##0;[Red]$#,##0")&" under this plan, given th
assumptions cited."

I know the formatting syntax is correct (it works on a stand-alon
basis), but the embedded number always prints black. Is this formattin
simply not available within a text string

 

[JE McGimpsey]

Worksheet functions can't affect cell or character formatting, including
colors.

You could do what you're after with an Event macro instead of a formula.
Post back if you're interested...

 

[jerrym]

I am interested. Thanks.

*Worksheet functions can't affect cell or character formatting
including
colors.

You could do what you're after with an Event macro instead of
formula.
Post back if you're interested...

I am embedding a reference to a cell within a text string. Th contents
of the referenced cell can be a positive or negative number. I wan the
number to print blue if positive, red if negative. The actual text
string is as follows:

="By the end of the planning horizon (December 31, "&SETUP!C23&") you
can expect to have accumulated "&T13&" of approximately
"&TEXT($S$13,"[Blue]$#,##0;[Red]$#,##0")&" under this plan, give the
assumptions cited."

I know the formatting syntax is correct (it works on a stand-alone
basis), but the embedded number always prints black. Is thi formatting
simply not available within a text string?

 

[JE McGimpsey]

One way:

Put this in your worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_Calculate()
Const s1 As String = "By the end of the planning horizon " & _
"(December 31, %1), you can expect to have " & _
"accumulated %2 of approximately %3 under this plan, " & _
"given the assumptions cited."
Dim sOut As String
Dim sValue As String
Dim nCI As Long
Dim nStart As Long

sOut = Application.Substitute(s1, "%1", _
Sheets("Setup").Range("C23").Value)
sOut = Application.Substitute(sOut, "%2", Me.Range("T13").Value)
nStart = InStr(sOut, "%3")
With Me.Range("S13")
sValue = Format(.Value, "$#,##0")
nCI = IIf(.Value < 0, 3, 5)
End With
With Me.Range("A1")
.Value = Application.Substitute(sOut, "%3", sValue)
.Characters(nStart, Len(sValue)).Font.ColorIndex = nCI
End With
End Sub

Change Range("A1") to your destination cell.

I am interested. Thanks.

*Worksheet functions can't affect cell or character formatting,
including
colors.

You could do what you're after with an Event macro instead of a
formula.
Post back if you're interested...

I am embedding a reference to a cell within a text string. The contents
of the referenced cell can be a positive or negative number. I want the
number to print blue if positive, red if negative. The actual text
string is as follows:

="By the end of the planning horizon (December 31, "&SETUP!C23&"), you
can expect to have accumulated "&T13&" of approximately
"&TEXT($S$13,"[Blue]$#,##0;[Red]$#,##0")&" under this plan, given the
assumptions cited."

I know the formatting syntax is correct (it works on a stand-alone
basis), but the embedded number always prints black. Is this formatting
simply not available within a text string? *

 

[jerrym]

Thanks. It looks like we're on the right track. But the solution i
going to be a bit more complicated, I fear. The complete function is a
follows:

="By the end of the planning horizon (December 31, "&SETUP!C23&"), yo
can expect to have accumulated "&T13&" of approximatel
"&TEXT($S$13,"$#,##0;[Red]$#,##0")&" under this plan, given th
assumptions cited; and there is a 75% probability that you will hav
accumulated "&T14&" of, at worst, "&TEXT($S$14,"$#,##0;[Red]$#,##0")&
by then. The minimum amount necessary at that point to fund you
"&IF((SETUP!B20-SETUP!B17)>50,"estate","retirement years")&" is th
subject of further discussion."

And some of the referenced cells will have undefined values (#VALUE!
when the worksheet is opened (i.e., some are the result o
yet-to-be-run Monte Carlo simulations; some are based o
yet-to-be-provided inputs). How do I avoid "type mismatch" run-tim
errors (which had not previously been an issue)?

Thanks

 

[JE McGimpsey]

The technique I outlined can be extended for the additional text.

IMO, you should prevent the errors to begin with, not work around them.
The exact method should depend on what is causing your #VALUE! errors.
For instance, if the error is caused by a missing value, then something
like:

=IF(A1="",0,<your current function>)

will display zero rather than the error. Or you could substitute "" for
the zero if you wish to display a blank cell.

The advantage to this is twofold - 1) any errors on your sheet are
*real* errors, not simply missing data, flagging a condition that needs
to be fixed, and 2) you or your users don't get inured to having errors
appear on your workbooks, and therefore miss an error in logic or data
entry due to it seeming normal for #VALUE! errors to appear on your
sheet.

 

[jerrym]

Thanks, but the #VALUE! "errors" upon opening the worksheet ar
unavoidable, I believe. In particular, they are the result of function
that are looking for various percentiles of a distribution -- thi
distribution is created only when a Monte Carlo simulation is run b
the user. The distribution does not exist when the worksheet is opened
thus the percentile values are undefined at that point. This is
styandard feature of the Monte Carlo software add-in. More problemati
is that the percentiles are really defined only at the end of th
simulation -- they are undefined during the course of running th
simulation, causing the formatting run-time error to pop up at eac
simulated trial, i.e., thousands of times. Given this, would yo
suggest a different approach to the formatting question

 

[JE McGimpsey]

Don't know what add-in you're using, so I don't know if the author was
lazy enough to require #VALUE! errors, but I suspect that you're making
function calls to the add-in, and in that case, you can certainly
preempt the errors.