Colour numbers within my formulae

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Midnight Moocher - 23 Dec 2005 15:43 GMT

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.

Reply to this Message

Peo Sjoblom - 23 Dec 2005 16:07 GMT

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.

Signature

Regards,

Peo Sjoblom

> I have been playing around with the '&' function, allowing me to mix text
> with calculations and cell references.
[quoted text clipped - 18 lines]
> Many Thanks and Merry Christmas,
> Midnight.

Reply to this Message

JE McGimpsey - 23 Dec 2005 16:50 GMT

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

> I have been playing around with the '&' function, allowing me to mix text
> with calculations and cell references.
[quoted text clipped - 18 lines]
> Many Thanks and Merry Christmas,
> Midnight.

Reply to this Message

Midnight Moocher - 25 Dec 2005 17:11 GMT

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)?

Reply to this Message