Colour numbers within my formulae

[Midnight Moocher]

I have been playing around with the '&' function, allowing me to mix text
with calculations and cell references.

I have the following at the moment:

="Of the "&TEXT(J954,"£#,0.00")&" made this year, I have managed to save
"&TEXT(100/J954*J955, "#,#0.0")&"%"

Which gives me a cell displaying the following:

Of the £0.00 made this year, I have managed to save 0.0%

What I would like to do is show the numbers (£0.00; 0.0%) in the colour
blue. I don't want to add conditional formatting, just apply a colour so
that the number stands out.

I think this involves entering ;[Blue] somewhere within the equation but
I've tried a few combinations and have not been unsuccessful. Is it
possible, and if so, what do I need to do to the existing cell?

Many Thanks and Merry Christmas,
Midnight.

 

[Peo Sjoblom]

Not possible using formatting, you can never format part of a formula
you can copy and paste special as values and then highlight the number part
and format that part.

 

[JE McGimpsey]

This requires using a VBA macro, since formulae cannot change cell
formats (including color).

If you want to automate it, here's one way:

Private Sub Worksheet_Calculate()
Const sTemplate As String = _
"Of the £ made this year, I have managed to save %"
Dim dTotal As Double
Dim dSaved As Double
Dim nColor As Long
Dim nTotalPos As Long
Dim nPercentPos As Long
Dim sTotal As String
Dim sPercent As String
nColor = RGB(0, 0, 255)
With Range("J955")
If IsNumeric(.Value) Then dSaved = CDbl(.Value)
End With
With Range("J954")
If IsNumeric(.Value) Then dTotal = CDbl(.Value)
End With
sTotal = Format(dTotal, "£#,0.00")
If dTotal > 0 Then
sPercent = Format(dSaved / dTotal, "0.0%")
Else
sPercent = "(N/A)"
End If
nTotalPos = InStr(sTemplate, "£")
nPercentPos = InStr(sTemplate, "%") + Len(sTotal) - 1
With Range("A1")
Application.EnableEvents = False
.Value = Replace(Replace(sTemplate, _
"£", sTotal), "%", sPercent)
Application.EnableEvents = True
.Font.ColorIndex = xlColorIndexAutomatic
.Characters(nTotalPos, Len(sTotal)).Font.Color = nColor
.Characters(nPercentPos, Len(sPercent)).Font.Color = nColor
End With
End Sub

 

[Midnight Moocher]

Thanks for the response. I'll just make do with what I've got at the moment.

I feel like I've come a long way with functions within the cells themselves,
but I don't know much about Visual Basic script.

Speaking of which, any good beginners guides out there (that are fairly easy
to follow)?