Coloured backgrounds using IF

Hi I am looking to format teh colour of cells using their values

ie
equal to or less than 5 Green
6 to 14 yellow
15 and greater red

any help greatly appreciated.


0
Utf
12/8/2009 1:52:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
635 Views

Similar Articles

[PageSpeed] 14

You would use Conditional Formatting to do this - available on the
Format menu. Just follow the Wizard through and choose the Patterns
tab for background colour.

Hope this helps.

Pete

On Dec 8, 1:52=A0pm, Simon <Si...@discussions.microsoft.com> wrote:
> Hi I am looking to format teh colour of cells using their values
>
> ie
> equal to or less than 5 Green
> 6 to 14 yellow
> 15 and greater red
>
> any help greatly appreciated.

0
Pete_UK
12/8/2009 2:31:21 PM
Hi Simon,

Highlight the cells and choose Format + COnditional Formating
Press the Add button twice - this will give you three lines.
1 - Change Between to Less than or Equal to, type 5 - Click Format and 
choose Green
2 - Type 6 and 14 - Click Format and choose Yellow
3 - Change between to Greater or Eqaul to, typr 15 - Click Fornat and choose 
Red

Judith

-- 
Hope this helps


"Simon" wrote:

> Hi I am looking to format teh colour of cells using their values
> 
> ie
> equal to or less than 5 Green
> 6 to 14 yellow
> 15 and greater red
> 
> any help greatly appreciated.
> 
> 
0
Utf
12/8/2009 2:38:01 PM
Assume that you are having Values in A column like the below:-

A Col
4
5
2
6
6
3
15
14
13
12
8
5
88

Place the cursor in A1 cell and extend the selection by holding the shift 
and down arrow upto your desired cell, keep in mind that the active cell 
should be A1 (Active cell will have a white background after selection also) 

now goto format>>conditional formatting>>Condition 1>Formula is>>paste the 
below formula
=AND($A1<=5,$A1<>"")
Click Format>>Pattern>Colour>Green and give ok.

Then Click Add>>Condition 2>> Formula is>>paste the below formula
=AND($A1>=6,$A1<=14)
Click Format>>Pattern>Colour>Yellow and give ok.

Then Click Add>>Condition 3>> Formula is>>paste the below formula
=$A1>=15
Click Format>>Pattern>Colour>Red and give ok.

That's it!

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Simon" wrote:

> Hi I am looking to format teh colour of cells using their values
> 
> ie
> equal to or less than 5 Green
> 6 to 14 yellow
> 15 and greater red
> 
> any help greatly appreciated.
> 
> 
0
Utf
12/8/2009 2:52:01 PM
Reply:

Similar Artilces:

Using search to find an email
If I do a search for an email in Outlook 2003 and choose all mail folders, is there a way to see what folder the item was found in? It seems to just show me the message, but not the location, so I don't actually know where it is..... Thanks If you add fields to the advanced search window, it will show you where the item was found. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, Sande...

ISSUE: Using VBA to Delete a Column
Hello all, I'm using a VBA routine that creates a copy of a worksheet under a different filename. The routine needs to delete column (D) before saving the new file, however column D passes through a group of merged cells (A1 through D1). When the command reaches this point: > Columns("D:D").Select > Range("D2").Activate, the entire range of cells from A:D are selected. I could use the mouse to manually select column D and delete it, but why wont VBA perform in the same manner. Here is the entire sub if it helps: Sub Create_ECP_Copy() ' ' ...

Replace using wildcards
Hi, I'm a newbie to Excel and this group so please excuse my ignorance. I'm trying to do what should be a pretty simple find and replace. I want to replace: base=xyz*cntry=123*type=simple wist base=differentbase*cntry=123*type=simple I have to include the type field in my search and the asterik's are part of the text, not wildcards. The only thing I want to change is the base. I put this in for the search criteria: base=xyz*cnty=???*type=simple This finds all the lines I'm looking for so, so far, so good. For the replace string, I use this: base=differentbase*cntry=???*ty...

Background in Signature
Hi there, is it possible for Outlook 2002 to have a background image as part of the signature? I had a look here, http://www.howto-outlook.com/howto/signatures.htm but could not find anything about this. I tried to create a signature with a background and linked it to a file on the web, but could not get it to work. It does link my Stylesheet and other graphics though. Any suggestions? Deon No, because the signature is appended to the mail message and doesn't affect its overall format. You need to use stationery to get a background. -- Sue Mosher, Outlook MVP Author of Mi...

Using a custom nk.bin in an emulator
Hi My development team has compiled an nk.bin file for a custom BSP. and they have sent me the os image so i can start developing .net applications for it, i am not sure how to get the nk.bin on the device emaulator list so i can deploy my apps to it, any help would be appreciated. I havent been able to find anything on the web on how to add the os image to the list, unless i am not looking in the right place. Thanks Souhail What they should send you is a device SDK generated in Platform Builder for your target device. One of the choices that they have is to also compi...

Using More than 7 if functions in a formula
Help--I am totally new at this feature of spreadsheets. I am trying to create a formula that needs more than 7 if functions i a formula. I want to be able to type in a weight and it will automatically choos the value that should be assigned to it. The weights range from 110 t 260. I have found out that you can only use 7 IF functions in formula. I have put in the following formula up to the 7th I function, but don't know where to go from here: If(B4=110,.656,if(b4=111,.659,if(b4=112,.661,if(b4=113,.665,if(b4=114,.669,if(b4=115,.673,if(b4=116,.678 I have read something about VLooku...

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 ...

Refer to sheets without using tab name??
Cell formulas that refer to other sheets usually look like this: =Sheet2!A1 ' Get cell A1 from Sheet2 However, what if someone changed the name of the Sheet2 tab to something like "ThisIsMyTab"??? Does that mean I would have to manually change all possible formulas to the following: =ThisIsMyTab!A1 ????? I'm mostly curious if there's a way to use formulas that do not strictly rely on the names of always changing tab sheet names. thank you Hello, The best way is to test it by yourself. I...

Using Repair Permissions
Using Repair Permissions comes up quite often on this list. I ran across this on Apple Discussions today and thought I would pass this along. You do not need to Repair Permissions on a regular basis. There are only three occasions you need to do this: 1. When you have just installed something that required you to run an Installer, rather than just copying some software to a folder. This should be done for both Apple and non-Apple software. (Note Office 2008 will come with an Installer this time) 2. When you have been working on your OS X files or folders while booted from OS 9, or remote...

Preventing users from using preview pane?
Is it possible to remove the preview pane option from the view menu or prevent the user from being able to use it? You can set a policy to disable the menu command. The command bar ID should be 5514. The policy templates can be found in the Office Resource Kit http://www.microsoft.com/office/ork. You will want to download and install the Office Core Tools to get the policy templates. I don't think the policy is 100% bullet proof because it only turns off the menu item(s). If the user already had the preview pane enabled it will still be enabled when the policy is turned on. Please let ...

using screen...
Hi, I have problem with the query in the cascade combobox when the form name change. Is there a way to replace the following using screen.activeform [Forms]![frmCoFundReview]![Partner] => screen.activeform.[Partner] ? SF Not that I know. You could use a separate query for each form. Each query would have the correct form name for the combo box. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "SF" <samnangs@pactcambodia.org> wrote in message news:u%23z%23g6iyKHA.2644@TK2MSFTNGP04.phx.gbl... > Hi, > > I have probl...

Use Richedit v2.0 with VC5.0/MFC4.2?
Hi. I'm using VC 5.0 with MFC 4.2 and would like to add the extra functionality of the RichEdit v2.0 control. The supplied copy of 'RichEdit.h' supports v2.0, but if, (as recommended by MS in one of their articles), I change the class name in the *.rc file from "RICHEDIT" to "RichEdit20", then call 'AfxInitRichEdit2()', this function is not recognised, so 'RichEd20.dll' does not get loaded. (Obviously 'AfxInitRichEdit2()' doesn't exist in MFC 4.2) Is there a way to use RichEdit v2.0 under these conditions? Any advice welcome,...

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: > >...

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...

Can't save Excel using Save button,
When I try to save an Excel spreadsheet, I get a message saying "GWXL97.xla could not be found. Check spelling of file name and verify that the file location is correct." I have never named any of files by that name and don't know what it refers to. I have to hit the "X" button on the upper right hand corner and wait for it to ask me if I want to save it, then go through the save process. This happens even if I try to save changes to an existing excel document This may help... xl: close button is dimmed after groupwise is installed http://support.microsoft.com...

Storing Query Into Table
I have been trying to find some information about storing query sql strings in a table and calling upon that string to run query's dependant on user selections in a form. I have a split database and I want the users of the front end to create queries that everyone can use. Can anyone point me in the right direction on where to start. Thanks Craig There is a complete query by form applet at http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This allows users to select field, enter criteria, sort, group, and run. The results are displayed in a datasheet subform...

Using banner paper Publisher 2002
Can you use banner paper in Publisher 2002? I am fairly new to the app and I have searched high and low and can't seem to figure out if I can use it and if so how? Any thougths? After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Tracy Buthe <anonymous@discussions.microsoft.com>... > Can you use banner paper in Publisher 2002? I am fairly > new to the app and I have searched high and low and can't > seem to figure out if I can use it and if so how? What size banner paper are you using? -- Ed Bennett - MVP Microsoft Publisher http://www.mvp...

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. ...

Using a LAN connection with a dial-up option
I connect to my email provider through a LAN while in the office, but want to be able to use my outlook from a dial- up while traveling to connect to my email provider(not MSN email). I have tried to use MSN dial-up to establish a connection and then open Outlook. I am able to receive mail but not able to send mail to my email provider. I have checked the SMTP address and it is correct. I am not sure why I cannot connect to my email server to send email when I can connect and receive email. Anyone have any thoughts? Thanks, Terry ...

Export to CSV file using "|" as the delimiter
Does anyone know how to export an Excel spread sheet to a csv file using "|" as the delimiter instead of ","? You can (temporarily) change the separator by changing the windows list separator under control panel regional settings (number tab) to that | character. Or you could write your own macro to extract the data in whatever form you want: Some sample code to get you started: Earl Kiosterud's Text Write program: www.smokeylake.com/excel (or directly: http://www.smokeylake.com/excel/text_write_program.htm) Chip Pearson's: http://www.cpearson.com/excel/imp...

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....

to use data validation or not
hi, I am making a church donation file in Excel. We have now about a hundred plus donors. The number of new donors are still increasing. At present, I am using data validation with drop down list. My main problem is the every week there are about 2 or 3 new donors. Donors usually use aliases instead of their real names such as Amazing Grace, 008 or Love etc. The list is getting long. I have to update the names in the list first. I would like to ask for your expert advice, is it better to continue using the data validation list or type the name of donors? In case, I need t...

Change the background of Form in 4.0
Hi, I would like the change the background of all form through style sheet in CRM4.0. i.e When i open any contact records, i want to change the background of contact. I have two environment of CRM and want some difference between them. Thanks in advance -Mac Hi Jon, You can change CRM 4 form color using below JavaScript document.all.areaForm.style.backgroundColor = ' #FF9933'; document.all.tab0Tab.style.backgroundColor = 'cyan';//color to tab document.all.tab0.style.backgroundColor = '#FF9933';//color to tab background Hope this helps! Sam _______________...

How to delete all bookmarks when using Word 2007?
I need to be able to quickly delete *all* bookmarks that might exist in a document. This is part of our procedure in cleaning up files we received from a number of different authors. We have just upgraded to Word 2007 from Word 2000. In Word 2000, I was able to use the macro found under Method 1 at: http://support.microsoft.com/kb/184041 I can't seem to find a similar article for Word 2007. I *have* found "Add or delete bookmarks at: http://office.microsoft.com/en-us/word/HP012265321033.aspx But this is a one-bookmark-at-a-time approach that I would *really* li...

using constants as a propoerty value
lets say i want to set, on multiple forms, the header background color to #123456 (as shade of light blue) can i declare "const LtBlue = #123456" (where i would do this i do not know) and then set the property for the header background color to LtBlue (on each form) or do i need to add some code for the on-activate event of each form that sets the property to LtBlue? thanks in advance, -mark In a standard Module, near the top, under the Option Compare Database statement, declare your constant like this Public Const YourConstantName = YourConstantValue then in any cod...