how do I apply more than 3 conditional formats in excel

I want to create a themal analysis problem whereby the results of the 
calculation are displayed in a grid of cells (representing the shape of the 
item). I then want to apply conditional formatting which colours the cells 
depending on the value. I want there to be 10 colours ranges. I can do this 
easily for four colours using standard conditional formatting but I want to 
use 10 colours, is there a way to expand the conditional formatting?
0
1/30/2005 4:17:03 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
374 Views

Similar Articles

[PageSpeed] 16

Hi

couple of options - there's a conditional formatting add-in available for 
download at
www.xldynamic.com/source/xld.CFPlus.Download.html

or you can use VBA in a worksheet_change event e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B6:B10")) Is Nothing Then
With Target
Select Case .Value
Case 1: Target.Font.ColorIndex = 4
Case 2: Target.Font.ColorIndex = 3
Case 3: Target.Font.ColorIndex = 0
Case 4: Target.Font.ColorIndex = 6
Case 5: Target.Font.ColorIndex = 13
Case 6: Target.Font.ColorIndex = 46
Case 7: Target.Font.ColorIndex = 11
Case 8: Target.Font.ColorIndex = 7
Case 9: Target.Font.ColorIndex = 55
End Select
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

--- this turns the font of cells in the range B6:B10 a different colour 
depending on what value (between 1 & 9) is entered in the cell.

to use the code, right mouse click on the sheet tab of the sheet where you 
want the code to run and choose view code - copy and paste the above in 
changing B6:B10 to your range and the numbers 1 through 9 to your criteria 
(and, of course the colorindex values to what you want).

Hope this helps

Cheers

JulieD



"chetwyndthomas" <chetwyndthomas@discussions.microsoft.com> wrote in message 
news:F0D77A0B-DDDF-4FFB-9FD6-6786B457D76C@microsoft.com...

>I want to create a themal analysis problem whereby the results of the
> calculation are displayed in a grid of cells (representing the shape of 
> the
> item). I then want to apply conditional formatting which colours the cells
> depending on the value. I want there to be 10 colours ranges. I can do 
> this
> easily for four colours using standard conditional formatting but I want 
> to
> use 10 colours, is there a way to expand the conditional formatting? 


0
JulieD1 (2295)
1/30/2005 4:24:46 PM
Reply:

Similar Artilces:

Excel 2003 Problem
I had office 2003 installed in a system running XP Professional. I login to the domain with the domain user name and password and was able to work with my office. The other day i changed my domain machine but with the same domain name. But when i log in back with the domain user name i have problems with the the office suite. Outlook doesn't open at all. Office and excel asks the name when u open the document or excel sheet every time. I couldnt find a soultion. I even reinstalled the office suite, now i am able to open the outlook , word and excel, but each time a small window ope...

Message Options (Delayed Emails) #3
Hi all, I have a user on my network who uses the Message Options often to delay emails in the Outbox to a specific time. The problem is only with this user. Users OS is 2000 Pro/Office XP (Outlook 2002) and is not working offline. Server is Exchange server 5.5 LDAP not POP3. (For example, the user can write an email at 1pm and click send and the email will sit in the Outbox until the specified time the user has set in the Message Options dialog box.) The problem is that sometimes the messages work and sometimes they dont. Has anyone experienced this before? Thanks. ...

Date formatting problem
I have an unusual problem and would appreciate any help. I have a spreadsheet that has date values in it, such as "2/3/1972". In the formula bar it is correct (includes an = sign at the beginning), but it displays "1/1/1900" in the cell and uses that value in calculations. When I apply a different date format, it still shows some variation of "January 1, 1900" in the cell and I can't get it to change. Any ideas? Thanks. I think that excel is seeing your =2/3/1972 as a giant division problem. =(2/3)/1972 which evaluates to: 0.000338066 which is 01/00/...

Math based on text or formatting
Excel 2003 I have 2 needs. 1st cell range (d5:d75) How can I COUNT the number of cells in that range that have a specific text value (ex: "BS in Sci"). Would the same formula work if that range of cells were all drop downs and the selected drop down value was (ex: "BS in Sci"). 2nd cell range (c5:c75) How can I count the number of cells in that range that have the text color formatted "RED" Thanks much Dave First one: =3DCOUNTIF(D5:D75,"BS in Sci") and yes, this will work if those values are generated from pull-...

downloading excel and word
i would like to download microsoft excel and word to my pc, and i search download sites and i could not find any? i would like to download for free. anyone can help me on this thank you Sorry mate, You can't have them for free, they are commercial products sold at a price (or noone would have heard of Bill Gates!). Even if you managed to get them for free, it would be illegal!!! There's other stuff out there you can get for free, like Open Office, that will do most of what Excel and Word do, at least for the average user (and with file compatibility). Nikos "yaman" <...

Stock prices in Excel?
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: intel Hi Folks, Can I set up excel so that it downloads and displays the current stock price for a certain stock every time I open the document or refresh? I want to use my own workbook, and I'm looking for a free solution, although I might consider an inexpensive add-on. Thanks!! Yes, Excel will do that. Pop over to the Excel forum and they will explain how: https://www.microsoft.com/mac/help.mspx?product=Excel&app=1 On 30/03/08 6:22 AM, in article ee969b4.-1@webcrossing.caR9absDaxw, "graybell@officeformac...

Logarithmic graphs in Excel
Hello! I have some Excel data that lists the United States' Gross Domestic Product for each year from 1890 to 2000. The data looks like this: 1890 260.42 1891 271.87 1892 298.51 .... 1998 8495.65 1999 8848.225 2000 9156.6 I wanted to turn this data into a groovy looking line graph, so I tried using Excel's built-in Chart Wizard, but the resulting graph looks WAY too much like an exponential curve (not to mention the fact that it really doesn't all that good), so I want to turn it into a logarithmic graph. How can I accomplish this? Double click the Y axis, and on the Sca...

Help for Excel Chart problem
Hi I have a little problem. I have to prepare audience profile for some TV Channels using dimensions age, sex, monthly income and education. I want to combine metric - age and income in one chart and non metric - sex and education in other. So I dont know which chart to use. I think the most appropriate is the bubble chart (more I have the penetration as a bubble size). But I cannot think how to dispose the dimensions and data. Example: Channel 1 Age 12-18 30 % 19-34 20% 35-64 35% 64+ 15% Sex Male - 48% Female - 52% So I need to positioning this channel and compare with ot...

Printing Doubel-sided in Excel
Has anyone experienced issues attempting to print double-sided in Excel using a Canon IR C3220 (PS3 driver) or is this a feature not supported by Excel. Hi Anthony This is a printer setting. If your printer have this option it must work Another option is to do it with code http://www.rondebruin.nl/print.htm#odd -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony Spartalis" <anthony_spartalis@hotmail.com> wrote in message news:ORn5khavFHA.2556@TK2MSFTNGP15.phx.gbl... > Has anyone experienced issues attempting to print double-sided in Excel using a Canon IR ...

Big problem with Excel 97
Excel 97 on Windows NT 4.0 SP6 After I create a new file and save it, it takes a very long time (about 30 seconds) to close the file. I am working with lots of Excel files and it's intolerable. Please help me with this. Thanks. ...

Total in every Page in Print-out in Excel
Is there a possibility that automatic page total be included in th foote -- Message posted from http://www.ExcelForum.com Hi This is a builtin feature in Excel since...some time! See View, Header and Footer You should come up with &[Pages] in your footer HTH Cordially Pascal "elmergev >" <<elmergev.18r8a3@excelforum-nospam.com> a �crit dans le message de news:elmergev.18r8a3@excelforum-nospam.com... > Is there a possibility that automatic page total be included in the > footer > > > --- > Message posted from http://www.ExcelForum.com/ > ...

Change Needed
I have a neat VB code that I use in Excel. This code imports data from a text file into columns in an excel spreadsheet. The excel file has two worksheets, one is to input the .txt filename with a button for the VB code to do it's magic and convert everything I have in the text file, which usually has the same layout (in each txt file) in terms of what data I want columnized in excel. The code goes as follows.... Sub ConvertFile() Dim LCStart As Long Dim IQStart As Long Dim Lat1Start As Long Dim Lon1Start As Long Dim Lat2Start As Long Dim Lon2Start As Long Dim NbmesStart As Long Dim...

Screen and print formatting migrated files
I hope I am permitted to re-post but original was in completely the wrong place and I do not know how to move it. I cannot print a colleague's excel files properly. I received an excel file from a colleague's pc but the page formatting is completely changed when opened on my pc. Even if I ensure page setup is exactly the same, specify the same printer and make no changes to font etc. text which will appear OK on colleague's pc under print preview and when printed will over-run onto second page when opened in my excel. No problems within cells etc - just the overall page size. ...

Custom number format
Hello. At work we have an application that creates .csv files that we open with Excel. This list icludes product nubers that are ten digits long, many of which start with one or more zeros. For legibility I would like all of the numbers to show leading zeros if they have them. So I create a custom format of ten zeros (i.e. 0000000000). This works great, but the format is only in that workbook. Is there a way to create this custom format and have it always be available even when a new notebook is opened insted of having to re-create it every time? If so, can it be assigned to a new toolbutton...

Excel help #3
Hi, Two questions. First is, when performing a Pivot Table, is there a way of changing the source so that when you produce a pivot table, the months are in order. And, when you concatenate and join a surname and first name together, how do you then separate them ? Thanks "Gary Newman" <gary.newman@adecco.co.uk> wrote in message news:09f501c39882$44084930$a401280a@phx.gbl... > Hi, > > Two questions. > First is, when performing a Pivot Table, is there a way of > changing the source so that when you produce a pivot > table, the months are in order. > &g...

PowerPoint 2007 with EXCEL Chart
Good afternoon, Thanks in advance. I hope I am in the correct group since this is both an Excel and PPT question - I created a Spreadsheet with 10 tabs, on each tab there is one fairly simple chart with a secondary axis that show a % for one column. I have the chart with format looking correct in Excel, and copy / paste to a corresponding slide in ppt. Sometime, not all of the time the format changes especially on the secondary axis, it goes to a decimal value. A lot of the time the data labels will be missing from the columns as well. Not sure what I am missing, USUALLY ...

Move CRM 3.0 from default web site to virtual directory
Hi, I would like to move the current CRM 3.0 installation from the default website to a virtual directory. Is this possible? I would like to be able to access the CRM system by using http://portal.mycompany.com/crm. The reason is that I would like to install a certificate for portal.mycompany.com. There are a few other apps that I would like to publish on this server and then I will only need one certificate. The second app would be accessible through http://portal.mycompany.com/app2. Any help would be appreciated. Regards, Ren=E9 ...

Sending HTML format report is not working #2
I have been sending RTF report from Ms Access on the body of the email. I need to send it in HTML format instead, but it is not working. The body of the email displays the whole source instead of the HTML formatted body. I have realized that the email format shows "Plain Text" and "Rich Text" format only. If you have further questions, please do not hesitate to contact me. Below there is an example of what I am getting, please check: <HTML><HEAD><META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252"><TITLE>Renta...

I can't format 01/19/2004 to read January 19, 2004, please help!!
Dragged and dropped a series of dates and now I can't convert them to sort them properly. What to do?? Hi Paul, "paulonline66" <paulonline66@discussions.microsoft.com> wrote in message news:3BB832DA-34AE-44B4-9D73-6F178E9AEBF0@microsoft.com... > I can't format 01/19/2004 to read January 19, 2004, please help!! > Dragged and dropped a series of dates and now I can't convert them to sort > them properly. What to do?? With the date cells selected: Data | Text to Columns | Next | Next | Select the Date option and Select the DMY option in the dropdown...

umIMHrxkBX #3
Your Site Is Great, http://board.talibkweli.com/index.php?showuser=3891 need 5000 loan overnight here, ffq, http://board.talibkweli.com/index.php?showuser=3895 personal loan for poor credit business, 013, http://board.talibkweli.com/index.php?showuser=3893 with no credit check loans, 743894, http://board.talibkweli.com/index.php?showuser=3889 Guaranteed high risk personal loans, nutdki, http://board.talibkweli.com/index.php?showuser=3892 guaranteed online personal loans bad credit, =))), ...

MYOB to Excel
Is there an Excel expert out there that also knows MYOB? I am trying to produce CATEGORY reports in the MYOB program that will show the previous year's details alongside the Current Year's Selected Period and Year To Date columns. ie The column headings should be: Account# Account Name Selected Period Year To Date Last Year's Selected Period Last Year's Year To Date. MYOB does export to Excel but MYOB can't produce such a report and I wonder if someone has already produced a workbook that would manipulate the exported data to produce a report in the manner descri...

format cells: divide by 100
I need to a Custom format for 3 cells in a worksheet. I want to enter value in any of those cells and have the value divided by 100. Fo example, I want to enter a 3 digit number like 427 and have its valu changed to 4.27. I have looked at Custom formatting and can't figure out how to do it. I know I can go to Tools/Options/Edit tab and check the Fixed decima box. That changes the whole worksheet. Can I do that for just 3 cells? Is there any way of doing this? Thanks for helping -- Message posted from http://www.ExcelForum.com Hi without VBA (using an event procedure) this is not po...

Problems with Window in Dialog Owner Draw? #3
I have the following problem. When my app calls HttpOpenRequest() for third time it allways fails. After that each call to HttpOpenRequest() fails. After several fails, even COM doesn't work (each try to create any object fails). It seems that I am running out of resources, handles or memory (because of memory leaks)? How can I check what exactly is wrong? Can I use Windows Task manager? How? What is the maximum number of handles per one executable? You can use Task Manager to check for resource usage. You can look in the Output window to see a list of memory leaks when your debug pr...

Excel Postcode list to map
Hi, Sorry if you've already had this on the "misc" group but no reply:- I have a list of UK Postcodes in a Spread Sheet in Column A, and a List of Date/Times in Column B. I'd like to plot these on a map so that lines are drawn to show show the route in time order. Does anyone know of an excel addon, programme or website that will allow me to do this from a worksheet? -- Rich http://www.rhodes-pefkos.co.uk http://www.rhodes-lardos.co.uk http://www.rhodes-kolymbia.co.uk "Rich" <rich@richdavies.com> wrote in message news:0HhHo.35263$sK1.32443@newsfe21.i...

SUMPRODUCT help #3
Help please I'm trying to count the number on items sold on a particular month using SUMPRODUCT but I'm getting the wrong answer This is what I use for each month (Feburary) for this example: =SUMPRODUCT((MONTH($L$2:$L$5973)=2)*(YEAR($L$2:$L$5973)=2007)*($U$2:$U$5973=$A4)) The item is in A4 the date is in column L and in Column U are all the mixed items. I have checked the date format and its ok. Can any one help please? Thanks josa Odd. I would begin by testing each part of the formula. For example: 1) =SUMPRODUCT((MONTH($L$2:$L$5973)=2)) 2) =SUMPRODUCT((YEAR($L$2:$L$5973)=200...