Colour numbers within my formulae

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. 


0
12/23/2005 3:43:36 PM
excel 39879 articles. 2 followers. Follow

3 Replies
760 Views

Similar Articles

[PageSpeed] 18

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.


-- 

Regards,

Peo Sjoblom

"Midnight Moocher" <jonathan.matthews73@ntlworld.com> wrote in message
news:IWUqf.11898$f7.1101@newsfe3-win.ntli.net...
> 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.
>
>


0
terre081 (3244)
12/23/2005 4:07:10 PM
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




In article <IWUqf.11898$f7.1101@newsfe3-win.ntli.net>,
 "Midnight Moocher" <jonathan.matthews73@ntlworld.com> wrote:

> 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.
0
jemcgimpsey (6723)
12/23/2005 4:50:31 PM
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)? 


0
12/25/2005 5:11:48 PM
Reply:

Similar Artilces:

page numbering #19
My page numbers don't show up when article is printed. I like them on the bottom of the page. queen of flowers wrote: > My page numbers don't show up when article is printed. I like them on the > bottom of the page. Do other objects in the same vertical position show up? It's conceivable that you have placed them in your printer's non-printable area. See http://ed.mvps.org/Static.aspx?=Publisher/horidiag -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

division formula
When creating formula,I use sum to add, product to multiply, what do I use to divide? If you're adding two cells, you could use: =a1+a2 For multiplying: =a1*a2 For dividing: =a1/a2 And for subtracting: =a1-a2 Terrie wrote: > > When creating formula,I use sum to add, product to multiply, what do I use to > divide? -- Dave Peterson ...

Message colour ?
Hi All Someone recently sent me a email that shows up red in my message window ( I don't use the preview pane ). I don't have any message rules that would change the colour of that message or anything like that and the priority of the message is normal. Anybody have any ideas as to how this is done ? OL 2002. Cas It's done with the default automatic formatting rules -- View | Current View | Customize Current View | Automatic Formatting -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers ...

How do I sort hexidecimal numbers in an excel spreadsheet?
When I attempt to sort hex numbers in a spreadsheet I get incorrect results. The cells are formatted as text (else the leading 0 would be dropped). I would like sort results to run from 0000 - ffff. any ideas? First run a sort. Then create a second sorting column and stick an X where it is numbers and leave it blank when you have letters. Or vice versa. I think you have some cells stored as a number. Force them to text. Run a sort and choose "Sort numbers and numbers stored as text separately". "Jane Martin" wrote: > When I attempt to sort hex numbers in a...

simple countif formula
Column B in my spreadsheet consists of 10 rows with one letter in each cell. I would like a formula to count cells B1,B3,B5,B7 and B9 only if the value in each of those cells is "H". I have tried a simple formula like this =countif(B1,B3,B5,B7,B9,"H") but it does not work. Thanks for your help with this formula. -- Brian Try =SUMPRODUCT(--(MOD(ROW(B1:B9),2)=1),--(B1:B9="H")) HTH Bob "Brian" <Brian@discussions.microsoft.com> wrote in message news:0F4B5C54-D6DC-47E5-A198-5AD7FE281C5E@microsoft.com... > Column B in ...

round up the number
Hi, I have a table below: A B C 1. 123.60 12 2. 178.40 8 How can I set a formular to get the following results: C1 = 124 * B1 (A1's 123.60 round up to 124) C2 = 178 * B2 (A2's 178.40 round up to 178) thanks in advance! Lowan -- Lowan Try: =ROUNDUP(A2,0)*B2 "Lowan Chan" wrote: > Hi, > > I have a table below: > A B C > 1. 123.60 12 > 2. 178.40 8 > > How can I set a formular to get the following results: > C1...

formula question #19
Need help on 2 formulas. here the work sheet cell A1 will have input either letter DO then a number (i.e. D102) or just the number (i.e. 134) cell A2 & A3 will input only numbers. (i.e. 9) 1st formula Cells A4 will add A2 + A3 only when cell A1 starts with the letter D (ie D102) Cell A5 will add cells A2+A3 only when cell A1 does not start with the letter D. (ie 134) Can this be done? One way in A4 =SUM(A2:A3)*(LEFT(A1)="D") in A5 =SUM(A2:A3)*(ISNUMBER(A1)) you could also use an IF function =IF(LEFT(A1)="D",SUM(A2:A3),0) =IF(ISNUMBER(A1),SUM(A2:A3),0) -...

Colour Rows in Datasheet based on three criteria
I was wondering whether it would be possible to colour rows in a datasheet (or continuous form) based on 3 criteria. Scenario is I have a series of jobs that can be either unassigned, assigned and finally "assigned and completed". I would like all rows that are "unassigned" to be one colour; all assigned jobs to be another colour and the final section to be a further different colour. thanking you in anticipation PMK On Mon, 17 Dec 2007 09:05:01 -0800, PMK <PMK@discussions.microsoft.com> wrote: >I was wondering whether it would be possible to colour r...

help create a formula using the IF command with AND
I need to create a formula for below: IF A2 is greater than A1 AND A4>A3 then A4 should be multiplied by .04, if not then A4 should be multiplied by .03. Can someone turn that into a formula for me? I'm sorry I should clarify the "if not", if A2 is not greater than A1. =if(and(a2>a1,a4>a3),a4*.04,a4*.03) or =a4*(if(and(a2>a1,a4>a3),.04,.03)) or =a4*(.03+.01*(and(a2>a1,a4>a3))) They'll all evaluate the same, so you can use the one that is easiest to understand. joe54345@gmail.com wrote: > > I need to create a formula for below: > >...

Maximum number of recepients for an e-mail message?
What is the maximum number of recepients I can have for an e-mail message using Outlook 2003? Aloha Ed, Depends upon a lot of factors - including what settings your e-mail server has. There isn't a hard and fast answer. -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com Microsoft OneNote FAQ: http://www.factplace.com/onenote.html > What is the maximum number of recepients I can have for an e-mail > message using Outlook 2003? > ...

Publisher 2003 Page Numbering
Is it possible to number the pages in a Publisher document starting from a specific page number (eg Page 35). If it's possible, how do you do it? The help files don't... TIA GG GordonG wrote: > Is it possible to number the pages in a Publisher document starting > from a specific page number (eg Page 35). > > If it's possible, how do you do it? The help files don't... > > TIA > > GG ====================== Try going to...Insert / Section -- ******John Inzer******** **MS Picture It! MVP** *****Digital Image****** Highlights and FAQs http://tinyurl....

Colour changes
Greetings from Downunder One of my colleagues sent me this, something of a puzzle. She is using XP and Office 2003:- In My Documents I have a folder of PPTs of hymns and songs that we use in church. I file any new songs here making sure that they are a golden yellow text on a very dark blue background. This is the way it has been for about 4-5 years. This morning I went to use some of them in the presentation for this coming Sunday - and would you believe they are now ALL pale yellow on a black background. Every last one of them - and each song is is a separate PPT file. ...

can i match a colour to its wavelength in nm
I have no clue what nm means? http://www.nattyware.com/pixie.html -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "aquakem" <aquakem@discussions.microsoft.com> wrote in message news:C5542A16-0420-47AC-A1F1-F7C4E6A11B80@microsoft.com... > Mary Sauer <gsauer@mycolumbus.rr.com> was very recently heard to utter: > I have no clue what nm means? Nanometres. > http://www.nattyware.com/pixie.html Won't do it. http://members.cox.net/astro7/color.html claims to have a FORTRAN program that will do it....

Why is colour printouts not same colour as in word?
When I print my logo from Word the colour is correct. When I copy it to Publisher and print it from there the colour comes out wrong. What could be the problem? Try importing the image rather than copy/paste. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "DvdM" <DvdM@discussions.microsoft.com> wrote in message news:E59B7744-C2FA-4A06-9778-96690CFFFE86@microsoft.com... > When I print my logo from Word the colour is correct. When I copy it to > Publisher and print it from there the colour comes out wrong. What co...

list of sequential numbers
I would like to make a colum of sequential numbers say beginning with 401 and ending with 499. is there a way to do this without typing in each individual number? Hi see the answer under your originial question titled "numbers in columns" Cheers JulieD "kellyaek" <kellyaek@discussions.microsoft.com> wrote in message news:67FF6C4E-4AFC-4E23-8EC0-987A682318F4@microsoft.com... >I would like to make a colum of sequential numbers say beginning with 401 >and > ending with 499. is there a way to do this without typing in each > individual > number? ...

Change bkgnd colour for some words in CRichEditCtrl?
I'd like to be able to change the background colour for some of the text in a CRichEditCtrl. I'm comparing two files side-by-side in these controls and need to be able to highlight some text to illustrate the results. I could just change the text colour and make the font bold and/or larger, but highlighting similar to when text is selected would stand out better, especially if I could also change the text colour to suit. Is this possible? (I've seen it in some programs, but don't know if a CRichEditCtrl was used.) Any help appreciated, Steve C. ...

Reducing Number Precision
I have a large worksheet made up of numbers of high precision - about 8 decimal places. Is there a way I can reduce this in excel to say 2 decimal places as I do not require a precision higher than that? If they are formulas you can use =TRUNC(A1,2) if they are constants you can use a number format with 2 decimals, then apply temporarily tools>options>calculations and precision as displayed Note that the latter will round the second digit. Then you can remove it -- Regards, Peo Sjoblom "lcs34" <lcs34@cam.ac.uk> wrote in message news:9f6601c43455$422cbff0$a3012...

colour recognition
hi guys, i may be chasing the impossible here, but here we go anyway. ive got cells in my spreadsheet formatted so that they change colour depending on what value is input. basically, i want to know if its possible for excel to recognise how many cells of a certain colour i have and then display the results numerically. is this possible? yet again, your really helping me out and it would be greatly appreciated. If you're using Format|conditional formatting to color those cells, then I think it would be easiest to just duplicate those rules in helper cells and then use those helper cel...

Removing Border lines and colour for All sheets
Hi Everyone, is it possible to write a macro to run every sheet and just remove border lines and colours for all cells in a sheet for every sheets in a workbook? I know macro is like this but can't really think how I can loop this. Can anyone help? Thanks alot Cells.Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borde...

Auto numbering now allowing me to restart numbering
I have a document with multiple lists and Word will not allow me to restart numbering of lists once I get to a certain point in a lengthy document. When I highlight the items to be renumbered and go to Format>Bullets and Numbering, the Restart Numbering option is grayed out. I have a rather convoluted work around that may help with the diagnosis of the problem. In order to force it to renumber, I add an extra line to the previous list. So for example if the last item is 10., I hit ENTER and it gives me 11. I can select 11, and it will allow me to restart numbering on that one...

Align Numbers To The Right
I am new to Excel and was practicing with a worksheet. I wanted only to place a $ sign at the first number in the column. When I go to the next line without using a $ sign and type only my number the alignment is slightly off to the right. For example: $ 91, 000 9,000 How can I correct this? Thanks, CJ Try highlighting the cells, then Rignt-click > formatCells > AlignmentTab > select Right in the Horizontal: box > OK Vaya con Dios, Chuck, CABGx3 "C J" <weshom@comcast.net> wrote in message news:Feedn...

Change color of cell after user has input a number
I have a spreadsheet that populates a number of cells. The user has the option to change the cell values. Now, ifthe user changes a value, I want the value to be of a different colour. Say the code-generated value of cell B1 is 137 (black colour) if the user changes the value to 200, I want the colour to be red Is this possible? Thanks in advance... Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B1:B10" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With T...

Number Range Format Type
I want to have a format for my cells that basically means "the data in this cell specifies a range of integers". For example, it might say 1-4, or 2-7, etc. Or it might just say 1 which is the same as 1-1 (low val is 1 and high val is 1). Is there a way I can have a format code for this and use a custom format? Later on in the worksheet, I want to be able to extract the min and max values of this range from the cells. The application of this is that a certain column is going to be used for holding ranges of numbers. Then at the very bottom I want to add up the ranges to produce...

highlight duplicate numbers
If I enter a number into any cell which has already been entered into another cell, regardless of row or column, is there any way in which my attention could be drawn to the fact that this number is already in the worksheet, i.e. by highlighting or something similar. Conditional formatting will do it with a formula of say =COUNTIF(A:IV,A1)>1 -- HTH Bob Phillips "css" <css@discussions.microsoft.com> wrote in message news:D83A67B2-9C17-4094-A25F-F9C4B1A7DAD3@microsoft.com... > If I enter a number into any cell which has already been entered into another > cell,...

How can I begin numbering pages(Pub) on first page after inside
I want to begin automatic page numbering on the actual third page. In other words, the cover and inside cover as well as the back cover and inside back cover will not be numbered. Can this be done? What version Publisher? Go to page 3, Insert, Section, Check Begin a section with this page. Start at page 1. Insert page numbers, if you are viewing a single page spread you will have to apply the page numbers on both sides. Create a new master page. Apply to the last page and cover. -- Mary Sauer http://msauer.mvps.org/ "bamatoni" <bamatoni@discussions.microsoft....