multiple conditional formats

How can I set up a worksheet for more than 3 conditional formats 
0
RC (240)
9/28/2004 1:57:03 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
272 Views

Similar Articles

[PageSpeed] 28

You can't. You would have to use a VBA macro to apply the formatting, which
would be static and not change with changes in the cell values -- you'd have
to run the macro again.

On Mon, 27 Sep 2004 18:57:03 -0700, "RC" <RC@discussions.microsoft.com> wrote:

>How can I set up a worksheet for more than 3 conditional formats 

0
anonymous (74722)
9/28/2004 2:01:36 AM
On Mon, 27 Sep 2004 18:57:03 -0700, "RC" <RC@discussions.microsoft.com> wrote:

>How can I set up a worksheet for more than 3 conditional formats 

You can use an event-triggered VBA routine.

To enter this, right click on the sheet tab and select View Code.  Then paste
the code below into the window that opens.

Edit the area of interest, values, characteristics of the cell you wish to
change,etc.  Look at help for color, colorindex, interior, font,etc for some
guidance on how to make appropriate changes.

For example, to assign different colors depending on value:

===================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range
Dim c As Range

Set aoi = [a1:a100] 'set this range to area to _
                     be conditionally formatted

For Each c In aoi
    Select Case c.Value
        Case Is < 1
            c.Interior.ColorIndex = xlNone
        Case 1 To 3
            c.Interior.Color = vbGreen
        Case 3 To 5
            c.Interior.Color = vbRed
        Case 5 To 10
            c.Interior.Color = vbBlue
        Case Else
            c.Interior.ColorIndex = xlNone
    End Select
Next c
=========================

--ron
0
ronrosenfeld (3122)
9/28/2004 3:25:56 AM
I have an additional question regarding more than 3 condtions. After I apply 
different colors to the cells, how do I ask the cells to turn BOLD. Where do 
I insert the command??

"Ron Rosenfeld" wrote:

> On Mon, 27 Sep 2004 18:57:03 -0700, "RC" <RC@discussions.microsoft.com> wrote:
> 
> >How can I set up a worksheet for more than 3 conditional formats 
> 
> You can use an event-triggered VBA routine.
> 
> To enter this, right click on the sheet tab and select View Code.  Then paste
> the code below into the window that opens.
> 
> Edit the area of interest, values, characteristics of the cell you wish to
> change,etc.  Look at help for color, colorindex, interior, font,etc for some
> guidance on how to make appropriate changes.
> 
> For example, to assign different colors depending on value:
> 
> ===================
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim aoi As Range
> Dim c As Range
> 
> Set aoi = [a1:a100] 'set this range to area to _
>                      be conditionally formatted
> 
> For Each c In aoi
>     Select Case c.Value
>         Case Is < 1
>             c.Interior.ColorIndex = xlNone
>         Case 1 To 3
>             c.Interior.Color = vbGreen
>         Case 3 To 5
>             c.Interior.Color = vbRed
>         Case 5 To 10
>             c.Interior.Color = vbBlue
>         Case Else
>             c.Interior.ColorIndex = xlNone
>     End Select
> Next c
> =========================
> 
> --ron
> 
0
HWade (9)
10/11/2004 3:59:04 PM
On Mon, 11 Oct 2004 08:59:04 -0700, "HWade" <HWade@discussions.microsoft.com>
wrote:

>I have an additional question regarding more than 3 condtions. After I apply 
>different colors to the cells, how do I ask the cells to turn BOLD. Where do 
>I insert the command??

You do that in the same segment where you set the color.

For example:

	Case 1 To 3
            c.Interior.Color = vbGreen
	    c.Font.Bold = True

Don't forget, though, that in the instances where you don't want it to be Bold,
that you should explicitly set it to False.  In my example, that would be for
Case < 1 and Case Else.


--ron
0
ronrosenfeld (3122)
10/12/2004 5:48:36 PM
Reply:

Similar Artilces:

Exchange rich-text format
What are the results from the client side if I change my Exchange 2003 server to "Alwayes Use" Exchange Rich-Text format from "Determined by individual user settings"? On Thu, 22 Jun 2006 08:31:01 -0700, CK <CK@discussions.microsoft.com> wrote: >What are the results from the client side if I change my Exchange 2003 server >to "Alwayes Use" Exchange Rich-Text format from "Determined by individual >user settings"? Depends on what your users are sending messages as. Most will be using HTML or RTF anyway unless you have some policy in p...

newsletter formatting
I have designed a newsletter in Publisher that I e-mail as a message. The format is saves as HTML. The original recipient can view the newsletter fine, however, when the newsletter is forwarded the formatting gets rearranged. How can I keep the original newsletter the same for orignal recipients as when it gets forwarded? Thanks, Fredrik Fredrik A <Fredrik A@discussions.microsoft.com> was very recently heard to utter: > How can I keep the original newsletter the same for orignal > recipients as when it gets forwarded? Convert to PDF using an application such as PrimoPDF (www...

Copy and Paste not saving format changes
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, I just bought Microsoft Office for Mac early this month and am still getting used to it. Can someone help me with this: I have several documents typed onto a notebook layout with lots of bullet points, color changes, cross outs and the like. I wanted to copy and past all of that into a new document but when I did ALL of the formatting changes were lost. There were no bullet points, so the text flushed left, yada, yada, yada. I looked around the toolbar to try and find a setting that would allow me to do the cut an...

Multiple user names at one bank or brokerage
I have 3 accounts at one bank. I have 3 corresponding accounts set up in MS Money 2007. One of them is setup for online banking while the other two are not. I now want to set the other two up for online banking but I can't find a way to specify the unique login info for each of the other two accounts. ...

If statement with formatted text
Hi, Is there any way to have an if formula such as: If (A1="Active", "KAE",KPE") where the two letters after the K are formatted as subscript? The best I've done is to paste a picture over the cell. The picture's formula refers to named formula that selects one of two cells, the one with correct string. One cell contains KAE and the other KPE with the subscript. However, it means that I'm using a picture and it would be much nicer if I could just do it in an If formula. I hope that makes some kind of sense, and thanks in advance for your help. D...

Formatting cells from internet
I have copied and pasted a chart from a website. (The website will not let me import) The cells are showing numbered values, but when i go to put the formula in it is giving me a zero like it cannot read the numbers in the cell. That is on 3 seperate columns. On the very last column it is reading the numbers I cannot make it recognize that those columns are numbers to be used in formulas Thanks for your help! Use Dave McRitchie's Trimall macro to clean up the data. Then copy an empty cell, select all your data and do Edit / PasteSpecial / Add and it will coerce all data back to nu...

Conditial Formatting Not Available
After trying to use conditional formatting (which didn't work btw) now the Conditional Formatting button in the ribbon isn't available or disabled. I tried Compact and Repair but it didn't work. Any ideas? James Duh. I was selecting a label instead of the field. "JamesJ" <jjy@adelphia_darwin.net> wrote in message news:uJhfSCSKIHA.1324@TK2MSFTNGP06.phx.gbl... > After trying to use conditional formatting (which didn't work btw) now > the Conditional Formatting button in the ribbon isn't available or > disabled. > I tried Compact and Repair b...

Export query to Excel with formated heading
When export from query to Excel, I'd like to turn some columns into heading row on top of Excel sheets how to turn this: LastName FirstName SID Section Instructor Doe John 1234 0545 Su Doe Jane 2345 0545 Su into this? Class Roster Instructor: Su Section: 0545 LastName FirstName SID Doe John 1234 Doe Jane 2345 I think one way of achieving this would be to create a report in Access where you would group under instructor with the students in the detail section. From the report you can then use the analyze with Ex...

Saving html message as Draft changes text formatting...
WIN XP HE, OL 2002 Hi, I have recently noticed that whenever I write an email (using Word as editor) in html format, and instead of sending it, save it (to the drafts folder), the text itself changes format from my default to another one. It seems to change in the paragraph style which then changes the text format. The only change I recently made was to edit my signatures in html, rtf and plain text format. When I write a new email, it opens up with the signature already in it and perhaps there are format/style conflicts..? Tx for shedding some light into this. S As an added information, t...

Multiple accounts #3
I have 2 Hotmail and 1 MSN accounts set up in Outlook 2002. It shows that it sends and receives all 3 accounts successfully, but when I go to the Inbox of the Hotmail accounts, there aren't any messages. Thanks, Rick It says it send and received fine, but problems occur when you set up more than one HTTP account, so it is recommended that you only use one per profile ...

Multiple Domain e-mail addresses
We are running Exchange Server 2003 Enterprise addition and I am having a bit of a "brain-freeze" at the moment... Where in the Exchange Admin do I configure the server to accept e-mail from more than one domain. For example, we currently accept e-mail from mycurrentdomain.ca and would also like to accept email from mycurrentdomain.com. Please note that I have updated all of our DNS MX records as required, just need to make the changes on the Exchange server. Thanks in advance... Recipient polices-email address policy. "Raymond Spitzingle" <ray(nospam)@stuff...

Using two conditions in a formula
How do I write the following formula: I am in cell I6 If g6 AND h6 is blank, then blank, else I5 minus g6 plus h -- Richard Pit ----------------------------------------------------------------------- Richard Pitt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1533 View this thread: http://www.excelforum.com/showthread.php?threadid=27042 Richard, In cell I6, enter the formula: =IF(AND(G6="",H6=""),"",I5-G6-H6) Though you don't check for case when G6 is blank and H6 isn't, etc. HTH, Bernie MS Excel MVP "Richard ...

conditional sum returns not expected
Hi all. Using xl xp pro Here is conditional sum formula =SUM(IF(FAR_All_Data!$B$2:$B$1622=C$2,IF(FAR_All_Data!$B$2:$B$1622=C$3,IF(FAR_All_Data!$A$2:$A$1622=$A5,FAR_All_Data!$D$2:$D$1622,0),0),0)) Sorry about the wrapping. Where C$2 = 7001.4500 Where C$3 = 7001.4501 Where $A5 = 54608001 Where Far_All_Data! contains data, all ref's verified However, all formulas return 0.00. As I copy down the formula Col (54608001) udates ...002, 003, 004, etc... Sometimes the expected result is 0.00 other times value should b greater than 0 Not sure how to read the formula. Does it say if, or, and? ...

HOWTO create a Mail Merge Template in MSCRM with multiple child records
Hi, I have created successfully a basic Mail Merge template in MS Word in Dynamics CRM 4.0 for Order Entity. My challenge now is how to design a template that retrieves the order details (product and prices) related to the order record? This sounds like a parent-child template. Any idea? On Jun 2, 1:26=A0pm, "Benjie Fallar III" <bfall...@hotmail.com> wrote: > Hi, > I have created successfully a basic Mail Merge template in MS Word in > Dynamics CRM 4.0 for Order Entity. > My challenge now is how to design a template that retrieves the order > details (prod...

how to search in excel with condition
First I must say that your site is great and it helps me a lot. Can you explaine to me how to do search in Excel ? explenation: I have 2 sheets on the same excel file. I need to search one column in the 2nd sheet and if i found the data I look for then I need excel to put this data in the first sheet on the same raw when the data is equel to. example: 1. on sheet 1 I have names of people. 2. on sheet 2 I have a cloumn that contains some of the names from sheet 1. 3. I need excel take each raw in sheet 1 and search sheet 2 for the same name. 4. if it found the same name in sheet 2 then...

Restrictions in format
How do I restrict some columns of having all text in UPPER CASE, some columns to have text only First In Capital Letter and some columns only in lower case?? Thanks JPG You can restrict using Data Validation but I would find that very annoying. Example............in DV>Allow>Custom =EXACT(A1,UPPER(A1)) will not allow anything but Uppercase. I would use event code to automatically change Case when anything is entered. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo ErrHandler Application.EnableEvents = False With Target ...

Multiple indexes on same fields in different order
I sometimes need to find the largest or smallest value in a field. If I put two indexes on the same field, one in ascending order and one in descending order, is Jet smart enough to use the proper index if a give it a grouping query with the condition Max or Min, or am I just wasting resources? Pete -- This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas. ...

Outlook/Access 2003
I've been doing a lot of research and purchased (but haven't completely read) Sue Mosher's book. However, before I spend much more time I'd like to find out if my idea is even feasible. The basic idea is to create an appointment system for multiple therapists that can be used by a receptionist and other staff. Using Outlook and Exchange is very nice but the appointment data needs to end up in an Access table immediately. We can purchase SBS 2003 with Exchange 2003 or we can buy a third party tool for calendar sharing whichever would be the easiest to use. I currently...

Conditional Format #14
That's what I was messing up! It was the ordering, not the formula! Thank you. Now if I have my =AND(E2="Y") for one and no formatting and my second =C2<TODAY() Red bold format, I should be able to mak rule 3 =C2<TODAY()-2 format Yellow bold and have anything 2 days clos to due yellow, YES -- Jimmytec ----------------------------------------------------------------------- Jimmytech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1447 View this thread: http://www.excelforum.com/showthread.php?threadid=26087 RE: =AND(E2="Y") You o...

Out of Office Assistant replies multiple times
The situation: The user of a fully patched Outlook 2003 SP1 sets an Out of Office reply using the Out Of Office Assistant.. The result: The first time someone sends a message while the OOA reply is turned on, they get the full text of the OOA reply as it is configured. The second and subsequent emails from that same sender gets a truncated version of the OOA message. In my testing, my second message (which isn't supposed to be replied to), got me 2 truncated replies. Examples from my testing: 1st reply: "I am out of the office and will return on Monday, February 28, 2005. At th...

Can i move multiple lines between workbooks
I have a .csv format file listing speed cameras, and the different speeds are identified by two characters at the end of each line - 30, 40, 50 etc. I would like to create separate files for each speed, but Excel won't allow me to cut/copy several lines to created a new file/workbook. Is there any way round this? Martin Ellis Sun, 16 Sep 2007 03:28:00 -0700 from Martin Ellis <MartinEllis@discussions.microsoft.com>: > I have a .csv format file listing speed cameras, and the different speeds are > identified by two characters at the end of each line - 30, 40, 50 etc...

Conditional Formatting
Hi I need some help with conditional formatting. I'm trying to highlight those cells which have a plus sign in them. Can someone help with the formula to enter into the conditional formatting dialogue? Grateful for any assistance. Best Wishes Hi Colin if the Plus sign + is part of a text string Select the column range first then =FIND("+",A2:$A$160) change the range to your needs. If the + sign is alone in the cell, you don't need a formula, just select > Cell value is > then select Equal to> and type the plus sign in the third box. H...

I am having trouble keeping numbers formatted in a Pivot Table
I have a pivot table that won't stay formatted. The data is formatted as currency in the worksheet that the Pivot Table is drawing off of. It shows up as a general number in the Pivot. I've tried formatting it in the Pivot but it doesn't stay when I refresh the data. "Preserve formatting" is checked on the Pivot table, so it SHOULD be preserving any formatting I apply to the pivot table, but that doesn't seem to be happening. I know I could record a macro to format this, but it is happening on multiple Pivot tables in the same workbook and that would be a pain ...

Vlookup, multiple times in one column summing corresponding
I have a list that contains common invoice numbers that appear multipl times in column A and need to use the vlookup function to find and su the corresponding amounts in column B. As you would be aware the following vlookup functio =VLOOKUP(A3,DATA,2,FALSE) will only return the one amount once i reaches a match and I need it to continue down the column and sum al matching invoice numbers. Column A Column B 145768 356.87 145769 678.90 145880 80.87 * 145769 103.55 145770 56.90 145769 78.32 145880 54.09 * The answer I�m looking to return is 145880 134.96 It has to be a vlookup sty...

IF Multiple Conditions
A B C D E Color Total Time Total Time 2 Time Allowed Y/NO BLUE 1:22:33 2:22:33 1:00:00 BLUE 2:22:33 3:22:33 2:00:00 BLUE 3:22:33 4:22:33 3:00:00 BLUE 4:22:33 5:22:33 4:00:00 BLUE 5:22:33 6:22:33 5:00:00 RED 6:22:33 7:22:33 6:00:00 RED 7:22:33 8:22:33 7:00:00 RED 8:22:33 9:22:33 8:00:00 I need an IF formula that: - will read IF condition in the A column is Blue it will bring back...