Bolding/etc based on formula results...

Hi folks,

I want to insert a formula into a cell, whereby if the results were
outside a certain range, the results would be bolded, and the cell
possibly infilled with a different background colour.

If the results of the formula fell within the certain range, then the
results would appear as normal type, and the background colour would
be the same as the rest of the spreadsheet.

Any pointers on how I can do this?

Cheers,

Dave
0
thadocta (3)
9/15/2007 6:17:03 PM
excel 39879 articles. 2 followers. Follow

3 Replies
452 Views

Similar Articles

[PageSpeed] 11

A formula cannot change a cell's format
But Format | Conditional Formatting can do just what you want.
Have a look at it, experiment, then return with questions.
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Dave Proctor" <thadocta@SPAMBAIT.westnet.REMOVE.com.au> wrote in message 
news:j88oe3ds59v598u6ov4u5justhu2hu8cok@4ax.com...
> Hi folks,
>
> I want to insert a formula into a cell, whereby if the results were
> outside a certain range, the results would be bolded, and the cell
> possibly infilled with a different background colour.
>
> If the results of the formula fell within the certain range, then the
> results would appear as normal type, and the background colour would
> be the same as the rest of the spreadsheet.
>
> Any pointers on how I can do this?
>
> Cheers,
>
> Dave 


0
bliengme5824 (3040)
9/15/2007 6:23:30 PM
See help on Conditional Formatting.

You can change font style and background color depending upon the value in a
cell.


Gord Dibben  MS Excel MVP

On Sun, 16 Sep 2007 04:17:03 +1000, Dave Proctor
<thadocta@SPAMBAIT.westnet.REMOVE.com.au> wrote:

>Hi folks,
>
>I want to insert a formula into a cell, whereby if the results were
>outside a certain range, the results would be bolded, and the cell
>possibly infilled with a different background colour.
>
>If the results of the formula fell within the certain range, then the
>results would appear as normal type, and the background colour would
>be the same as the rest of the spreadsheet.
>
>Any pointers on how I can do this?
>
>Cheers,
>
>Dave

0
Gord
9/15/2007 6:24:40 PM
On Sat, 15 Sep 2007 11:24:40 -0700, Gord Dibben <gorddibbATshawDOTca>
wrote:

>See help on Conditional Formatting.
>
>You can change font style and background color depending upon the value in a
>cell.

Exactly what I was after - ta muchly.

Dave
0
thadocta (3)
9/16/2007 5:10:53 PM
Reply:

Similar Artilces:

copy formula
In Sheet a, i have these info Employee Number hiring date 122555 apr 5, 2010 152666 apr 4, 2010 123554 apr 4, 2010 451225 apr 5, 2010 In Sheet b, i need this info (that pulls from Sheet a) Employee Number hiring date 122555 apr 5, 2010 451225 apr 5, 2010 How can I do a formula in Sheet b that will pull all the data from sheet a for a specific date e.g. apr 5, 2010. VLOOKUP http://www.ozgrid.com/Excel/excel-vlookup-formula.htm Or INDEX/MATCH http://www.ozgrid.com/Exc...

Formula Bar
In my previous copy of Excel, when I typed in an equation in the Formula Bar, it would give me a little message window that showed where I was in a particular equation. Now that I have a another copy, I don't know what the "on" switch is for this function. Please help, it's driving me crazy when I create very long equations. Thanks, AK AK it was introduced with excel 2002, you turn it on/off under tools>options>general>function tooltips -- Regards, Peo Sjoblom "ak" <anonymous@discussions.microsoft.com> wrote in message news:BB2440A2-2883-4...

error with formula
Hi guys here is the issue. Under column A, which is titled serial number, I have listed the formula =IF(ISNUMBER(B28)=TRUE,A27+1,""). What this does is that it detects if there is a date in column B and then inputs a serial number that is one more than the previous one. If not the cell in column A is just left blank if no date is detected. Under column D, entitled Receipt number, I have inputed the formula =IF(MOD(A27,8)=0,"","NA"). What this does is it detects the serial number in column A and checks if it is a muliple of 8. If it is the cell in column D ...

Copy formulas #3
Hello, I have a forumla in a cell which I need to copy and paste to a variable number of cell directly below. Currently I have set up a little macro that copies and pastes the formulas to the 250 cells below. Unfortunatley I never know how many cells the forumla needs to be copied to. What I really want to check whether cell A1 contains data, if it does then pastes the forumala, then look at A2, if that contains data then paste the forumla and so on until the cell checked is empty. I know this is possible but don't know how to do it. Please help me. -- sgrech -----------------...

Netsky, Bagle, etc
Hello, Is anyone else getting slammed with netsky or bagle virsuses? We are, just the last couple of minutes I received 200 message from my Exchange antivirus stating that it blocked these viruses. Is there something else going on. Everyone on my network is up to date. Our virus definitions are up to date. What should I do? I have turned off AV notification on all my clients' servers because it's just too overwhelming (some may like to keep the admin notifications enabled) - all you're seeing is that there are a lot of infected computers out there, alas. Chris wrote: >...

Exchnage 2000 Server based receipts
Greetings, Would like to have my exchange 2000 server send a customized reciept to customers when the message arrives at my server. I would also like to have it append a legal notice on all outgoing messages. Can this be done natively? Are there any decent third party products that will allow me to meet both my primary and secondary goals? Thanks, Mark ...

Formula Auto-Adjustment
Hello, I have a formula that references cells on another worksheet (say sheet1). The formula looks like this: =sum(sheet1:A1:A8) When I remove two rows (row 2 and row 3) from sheet1, the formula is adjusted automatically to: =sum(sheet1:A1:A6) However, this is not what I want. How do I keep the original formula sum(sheet1:A1:A8) even if rows are deleted? I tried sum(sheet1:$A$1:$A%8), but to no avail. Any help would be appreciated. Hi, Try =SUM(INDIRECT("'Sheet1'!A1:A8")) Be careful - your formula shows a ":" between the sheet name and the address it s...

Query based on ComboBox selection
I have a table "Master" with the following fields: Master_No Date Name Product Agent I want to have a form with combo boxes for the above fields and TextBox for date, and a subform having Query or table type. I want user to be able to filter the subform query based on the criterias chosen from the combobox and date enterted in the textbox. I believe I have managed to explain what I want. Anyhelp would be appreciated. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200705/1 I have just found out that I can use =Forms!NameOfFor...

How to make universal changes to a whole range of cells and formulas?
I can't seem to figure out how to make global changes to a whol worksheet of formulas. Is it possible to make changes to all cells at once? For example, i it possible to make all references in cells absolute references at th same time? Also, I would like to copy a range of cells on one worksheet, "master" (with formulas that refer to another worksheet, worksheet1, for example to paste into another section of the same "master" worksheet, bu keeping the original formulas referring to the same cells on different worksheet, such as worksheet2. I would much apprecia...

weird results
why is the result of typing =rand(200,99) in MS word is weird? See http://word.mvps.org/FAQs/Formatting/DummyText.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "BS Prithviraj" <BS Prithviraj@discussions.microsoft.com> wrote in message news:1BABF68F-955F-43D8-BE2E-76DB6FC548E3@microsoft.com... > why is the result of typing =rand(200,99) in MS word is weird? > ...

What does the ! stand for in Formula
In the formula =If (02="carports,"'input data' ! $B*X2*1000,"ERROR" What does the ! stand for in Formula It is a separator between the sheet name and the cell address... and I suspect your formula should look like this: =IF(O2="carports",'input data'!$BX2*1000,"ERROR") -- Steve "Luis" <Luis@discussions.microsoft.com> wrote in message news:15625906-7F84-43A2-BEDC-9C22C0EA6F6E@microsoft.com... > In the formula =If (02="carports,"'input data' ! $B*X2*1000,"ERROR" What > does the !...

Hide field or tab based on user or group
We need our admin users to see all tabs of the account form and have some of the tabs hidden from the general and sales users. Is there anyway in crm 1.2 to hide a tab or field from a user or group? If this is not possible in 1.2 can it be done in 3.0? I saw mention in 3.0 about filtering form views. Is that done by group membership? Thanks in advance -- Scott Afraid there is no supported way to do this in either version. There are some sultions you could do with javascript via the Onload of the form that "hides" the tabs, but that isn't a supported nor a very secur...

How to move the sum formula
Hi, I have to copy the sum formula from one column to the next I used (the range of second coloumn)=range of first column.formula The reason is I would like to roll forward the formula of month to another month Range(Cells(x, i + 2), Cells(y, i + 2))= Range(Cells(x, i), Cells(y, i)).Formula it works well except the sum formula cells. the sum formulas inside is again referencing the old location.what to do.Please help me out. Thanks, Santhosh I think it depends on what formula is in the original cell. Maybe... Range(Cells(x, i + 2), Cells(y, i + 2)).formular1c1 _ = Range(Cells(x, i), Ce...

Formula for numbers=color
I am trying to make a cell or the numbers in a cell turn red when they are certain numbers, like 20 for example. If I enter 20 in the cell it turns red. Trying to make a lotto sheet so I can easily find my lotto numbers. Thanks On 20 Sep 2004 16:32:22 -0700, vile5@comcast.net (Vile) wrote: >I am trying to make a cell or the numbers in a cell turn red when they >are certain numbers, like 20 for example. If I enter 20 in the cell >it turns red. Trying to make a lotto sheet so I can easily find my >lotto numbers. > >Thanks Look at Conditional Formatting (under the Format...

formula to calculate # of days between dates, excluding holidays
I am wondering if there is a formula out there that will calculate the number of days between two dates, but exlclude holidays. I know there is a formula that will calculate work days, but I need one to calculate all days of the week, not just work days. Assuming your dates are in A1 and B1, Create a RangeName called Holidays in an out of the way place and list your holidays there........then use =(B1-A1)-COUNT(Holidays) Vaya con Dios, Chuck, CABGx3 "abs2299" <abs2299@discussions.microsoft.com> wrote in message news:01F99198-5398-416E-8C3F-2179CF6ADC0E@microsoft.com.....

Copy records automatically to another sheet based upon constant criteria
I have 1000+ address lines (name, address, phone) in a single sheet. I would like to make a copy, which will update, based upon constant criteria. Ex: I would like to copy all records which have 561 area code and place them on another sheet, all 856 on another and so on. Changes made to the "master" sheet would automatically update on the appropriate sheet - additions, changes, etc... Can anyone assist? Thank you in advance. On Jul 1, 8:41=A0am, skiing <trpa...@chatt.com> wrote: > I have 1000+ address lines (name, address, phone) in a single sheet. > I would like to mak...

need to create a formula to create a timesheet but haven't a clue
Timesheet Formula: I am trying to create a formula to calculate whether the hours used are either time and a half between x-y hours. Completely useless on Excel. Depends upon the rules. For instance, if anything over 8 hours is overtime then =MAX(end_time-start_time-TIME(8,0,0),0) If it is any hours outside ofv pre-set start and end times, say 08:00 and 16:00 then =MAX(TIME(8,0,0)-start_time,0)+MAX(end_time-TIME(16,0,0),0) -- HTH RP (remove nothere from the email address if mailing direct) "AHurd" <AHurd@discussions.microsoft.com> wrote in message news:5FF79B03-92...

Clear Search Results triggers 'Outlook cannot display this view'
I am using Outlook 2002 under Windows 2000 (Server). Frequently (but not always) when I use the "Find" (search) function to look for a string of text in a particular mail folder the following error message occurs when I click the "Clear" link: Outlook cannot display this view. Could not complete the operation. One or more parameter values are not valid. Why? ...

Performance Issue with ownerdrawn controls in dialog based applica
Hello Everybody, I am using evc++ for creating multiple dialog based application with ownerdrawn button, edit and static labels for windows CE. Based on the input from user, i will launch the dialogs. Problem is that dialog creation takes time as all the controls are created in the OnInitDialog and each controls take a bitmap which again its a overhead as bitmap resources are very heavy. Is their any possiblity to create controls quickly so that all the controls are visible in short duration of time ? How to create controls if a dialog has more than 10 controls ? How to load jpeg, png f...

Help with Date Range Formula
I am helping with a spreadsheet and have had little success in trying to come up with a solution. Here's the problem. I will need the ability to have the formula reference a range of dates based on a max for each date of incident and return and answer. Here's and an example: Lets say I have 3 entries Name date of incident Average Monthly Wage John Doe 01/01/2008 4333. Jane Doe 01/01/2003 2888 Sally Doe 01/01/2009 3600 In the example above I would need to have the average monthly wage col...

Formula in word 2003
create a formula in a word form. I have a situation where I ned to provide an auto response based on the number d in a bokmarked cell "Risk1" I have tried the following but it brings up an error {if { risk1 } <="4""YES"}{if {risk1}>"4"<"10""Stop"}{if{risk1}>"9"<"17""Report"} The basic requirement is to provide a different response based on a number value in form field bookmark "Risk1" which can range between 1-4, 5-9, 10-16 and 17-25 each of the above ranges need a...

Removed names in formulas?
Hi, if I remove the name of a cell, which is used in a formula, given formula does not change it back to the address - it returns #NAME?. Does exist any code how to change it? Thanx Marian "Marian" <mcon@hnonline.sk> wrote in message news:OV$gizcqDHA.2404@TK2MSFTNGP12.phx.gbl... > Hi, > > if I remove the name of a cell, which is used in a formula, given > formula does not change it back to the address - it returns #NAME?. > > Does exist any code how to change it? > > Thanx > > Marian > I don't know about code, but you can do it easil...

Query calculated field based on another query
Hello, this is probably fairly straighforward but I just can't get the right syntax in building this query: I have the following table containing time records person, rDate, rHours (tRecords) I have a table with rates (tRates) person, Rate1, Rate2 I have a table with national holidays (tHolidays) hDate I need to have a query which returns the following: person, rDate, billed: rHours * if (wDate is found in tholidays then Rate2 else Rate1) any pointers greatly appreciated. thanks what I should have specified is that i have difficulty in trying to get the value calculated for the cust...

formula question #15
Hi. I'm working on a spreadsheet of soccer fixtures and results for a school project. I've done formulas to result in games won, games played, points etc. What I'm having problem with is the draw scores. Say Arsenal 2 v 2 Chelsea MUnited 1 v 1 NCUnited AVilla v Liverpool The formula I used is =IF(B1=D1, 1, 0) + IF(B2=D2, 1, 0) + IF(B3=D3, 1, 0) but if you haven't entered a score yet like for the 3rd row and the cells are empty it counts it as 1 as they are still equal. please help Thanks Try: =IF(and(B1=D1,B1<>""), 1, 0) ...

Updating a field based on criteria
Hi, I had this before, but cant figure it out now. Say we have a database that has a RecordID(Primary), COLLARID(Multiple), LOCATION(NUMBERS), DATE, NOTES(text) Example... RecordID, COLLARID, LOCATION, DATE, NOTES 001,112,64.112X97.152,16/03/2010,BLANK 002,112,64.115X97.155,17/03/2010,BLANK 003,112,64.119X97.155,18/03/2010,BLANK 004,115,60.295X97.000,15/03/2010,BLANK 005,115,60.302X97.005,17/03/2010,BLANK 006,115,60.305X97.007,18/03/2010,BLANK As you can see above, the NOTES Field is all blank. What I hope to achieve is a function or query that will take a look at my dat...