How do I set up more than 3 conditional formatting?

Hi,
I have 6 different investigation area, one single name each, and I would 
like to set 6 different conditional formatting. But I'm not able to set more 
than 3.

Any idea how can I manage it?

Many thanks
0
Utf
5/19/2010 9:23:01 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
786 Views

Similar Articles

[PageSpeed] 45

You can use a macro.

What are your conditions / desired formats ??
-- 
Gary''s Student - gsnu201003


"Pomodoro" wrote:

> Hi,
> I have 6 different investigation area, one single name each, and I would 
> like to set 6 different conditional formatting. But I'm not able to set more 
> than 3.
> 
> Any idea how can I manage it?
> 
> Many thanks
0
Utf
5/19/2010 10:44:01 AM
Hi Gary!
Sorry to answer you late, I supposed to receive a notification mail, but I 
didn't.
So, my conditions is like this:
if mycellvalue = "Services" then set cell pattern color to red else
if mycellvalue = "Technology" then set cell pattern color to gray else
if mycellvalue = "Green" then set cell pattern color to green else
if mycellvalue = "Service Innovations" then set cell pattern color to orange 
else
and so on for at least 6 different Innovation Areas.

-- 
Pomodoro


"Gary''s Student" wrote:

> You can use a macro.
> 
> What are your conditions / desired formats ??
> -- 
> Gary''s Student - gsnu201003
> 
> 
> "Pomodoro" wrote:
> 
> > Hi,
> > I have 6 different investigation area, one single name each, and I would 
> > like to set 6 different conditional formatting. But I'm not able to set more 
> > than 3.
> > 
> > Any idea how can I manage it?
> > 
> > Many thanks
0
Utf
5/19/2010 12:46:01 PM
.... or you could upgrade to Excel 2007 which allows 64 conditions!

"Gary''s Student" wrote:

> You can use a macro.
> 
> What are your conditions / desired formats ??
> -- 
> Gary''s Student - gsnu201003
> 
> 
> "Pomodoro" wrote:
> 
> > Hi,
> > I have 6 different investigation area, one single name each, and I would 
> > like to set 6 different conditional formatting. But I'm not able to set more 
> > than 3.
> > 
> > Any idea how can I manage it?
> > 
> > Many thanks
0
Utf
5/19/2010 2:27:01 PM
In my company pcs are still in 2003...
Which is the macro that could help me?
-- 
Pomodoro


"Vicar" wrote:

> ... or you could upgrade to Excel 2007 which allows 64 conditions!
> 
> "Gary''s Student" wrote:
> 
> > You can use a macro.
> > 
> > What are your conditions / desired formats ??
> > -- 
> > Gary''s Student - gsnu201003
> > 
> > 
> > "Pomodoro" wrote:
> > 
> > > Hi,
> > > I have 6 different investigation area, one single name each, and I would 
> > > like to set 6 different conditional formatting. But I'm not able to set more 
> > > than 3.
> > > 
> > > Any idea how can I manage it?
> > > 
> > > Many thanks
0
Utf
5/19/2010 3:04:06 PM
Try this by Bob Phillips from a google search

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10"    '<=== change to suit

    On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
            Select Case .Value
                Case 1: .Interior.ColorIndex = 3    'red
                Case 2: .Interior.ColorIndex = 6    'yellow
                Case 3: .Interior.ColorIndex = 5    'blue
                Case 4: .Interior.ColorIndex = 10   'green
                Case 5: .Interior.ColorIndex = 46   'orange
                Case 6: .Interior.ColorIndex = 8
            End Select
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

HTH
Regards,
Howard

"Pomodoro" <Pomodoro@discussions.microsoft.com> wrote in message 
news:D51F9BA3-C356-4EED-B8E4-76308D8D4581@microsoft.com...
> Hi,
> I have 6 different investigation area, one single name each, and I would
> like to set 6 different conditional formatting. But I'm not able to set 
> more
> than 3.
>
> Any idea how can I manage it?
>
> Many thanks 


0
L
5/19/2010 6:30:50 PM
Sample worksheet event code for 10 conditions and colors.

Adjust range, vals and nums to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A100")
If Intersect(Target, r) Is Nothing Then
    Exit Sub
End If
vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X")  'conditions
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 13, 15)  ' color fill
For Each rr In r
    icolor = 0
    For i = LBound(vals) To UBound(vals)
        If UCase(rr.Value) = vals(i) Then
            icolor = nums(i)
        End If
    Next
    If icolor > 0 Then
    rr.Interior.ColorIndex = icolor
    End If
Next
End Sub

This is event code.  Right-click on the sheet tab and "View Code".
Copy/paste into that sheet module.  Make your edits then Alt + q to return
to Excel.


Gord Dibben  MS Excel MVP


On Wed, 19 May 2010 02:23:01 -0700, Pomodoro
<Pomodoro@discussions.microsoft.com> wrote:

>Hi,
>I have 6 different investigation area, one single name each, and I would 
>like to set 6 different conditional formatting. But I'm not able to set more 
>than 3.
>
>Any idea how can I manage it?
>
>Many thanks

0
Gord
5/19/2010 6:38:11 PM
Reply:

Similar Artilces:

ABILITY TO SET A $ CHECK AMOUNT IN AP for SELECT CHECKS
I have several customers who need to be able to choose invoices to pay based on a total check amount. For instance all checks over $ 5000 go to a different signing authority or all checks under $ 5000 have a signature already on the check form ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree&qu...

Linker errors #3
Hi, I have been managing couple of MFC projects in VS .NET2003, but for a particular project this is the structure:- A.dll, DLL linking to static MFC. B.lib, Static library linked to static MFC C.lib, Static Library linked to static MFC. Now I have project dependencies on B and C in A. Well I get clashes for the below functions, which I have been overcoming using /FORCE linker option and logically it seems ok to me as all the memory allocations/deallocations go through one routine after resolutions. I am a bit wary about what I am doing and feel I could fall into subtle runtime issu...

multiple conditions in COUNTIF (Excel 2000)
I want to count the number of occurences of a specific range in a list of numbers. I.e. the numbers range from 0 to 24 and I want to count the number of "hits" that are between 12 and 13. COUNTIF seems the obvious if want to keep it simple. But it seems COUNTIF can handle only one condition (">12" or "<13", not both), not allowing "AND" to be part of the criteria. Any suggestions? "Johbou" wrote: > I want to count the number of occurences of a specific range in a list of > numbers. I.e. the numbers range from 0 to 24 and...

How do I set up Outlook to sync to Mobile Me?
I am trying to keep 2 PCs and and iPhone synced (mainly contacts and calendars) with Apple's Mobile Me. Everytime Outlook tries to sync (every few minutes), I get several error messages. I have checked for updates to Outlook and I am up to date. -Jim Since the error messages are likely the key to the problem, please post them exactly as they appear (in their entirety). "Jim" <Jim@discussions.microsoft.com> wrote in message news:B6F0F5C0-D461-45B8-821A-5BF63EB97751@microsoft.com... > I am trying to keep 2 PCs and and iPhone synced (mainly contacts and ...

Anyone know if the PST file format spec has been released yet?
Really need this badly... Thanks! Jack Never mind. Looks like they finally did about one month ago. http://msdn.microsoft.com/en-us/library/ff385210.aspx Jack "Jack Black" <jackisback@hotmail.com> wrote in message news:OO9%23Yg7xKHA.5936@TK2MSFTNGP04.phx.gbl... > Really need this badly... > > Thanks! > Jack > ...

Time format for workschedule
Hi, I'm looking for some help I'm looking to calculate timesheets, but instead of 2:15 in format of 2 hours 15 minutes, I'm looking to save as 2.25 where it's 2 hours and 0.25 hours I would take calculations from Cell A1 as start time of 8:00 AM and Cell A2 end time of 10:15 AM and result in Cell A3. any suggestions? Lifegaurd =(A2*24)-(A1*24) formatted as 'general' -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Lifeguard" <Lifeguard@discussions.microsoft.com> wrote in message news:FF061C3...

Formating Names
I have an example where the First and Last Name of my contacts are in a single column. I would like the first and last name in separate columns ie: John in Column A and Brown in column B any help would be appreciated. Richard It may as simple as Data>Text to Columns>De-limited by>Space. Then again, if you have cells with more than just two name, you may require something else. I would recommend Chip Pearson's site. http://www.cpearson.com/excel/FirstLast.htm Gord Dibben MS Excel MVP On Sun, 9 Jul 2006 12:14:05 -0400, "Richard Mahan" <dmachen35@co...

Setting Calculation always goes back to Manual. How to permanently have Task Panel dismissed?
Two issues. Since shortly, the setting in Options -> Calculation -> Calculation is always set to Manual, even after I have set it to Automatic and saved the document. After restart Excel is it set back to Manual. Irky. some documents, the task panel is always active after I open the document. Hoew to have it permanently closed? It happen in some documents. Bart ...

can I set outlook to send a message received as "new" message ?
can I send a received e-mail as a "new" message Netscape allows send as new Don Hocutt, you wrote on Thu, 22 Sep 2005 15:47:01 -0700: > can I send a received e-mail as a "new" message No. > Netscape allows send as new Use Netscape... ;-) -- Best Regards Christian Goeller Some misspellings, grammatical or linguistical mistakes found? All corrections would be appreciated! This is very simplistic. Sorry. The only way I know to pull this off is to open the email, copy its contents, paste it into a new message, and send new message. I do this on occasion when ...

Chart template not saving title formats
Hi - I have a chart saved as a template and everything is formatted correctly when applied to a new chart, except the chart and axis titles. The category and value axis values maintain the formatting that was stored with the chart. It's just the titles for the chart, category axis and value axis that I have to always reformat. Any suggestions? Thanks! Hi Tammy, chart templates will not save titles. Privacy was one of the concerns when considering that templates could be shared broadly without knowledge of what the template might contain. -- Thanks, Christopher This posting is p...

Data Forms #3
Hi I have a spreadsheet that I am filling in using a form. I have also a row at the bottom with blank rows in between with a running total. When I print there is a huge gap between the last entry in the form and the totals at the end. Is there a way that the form can enter the next line and push down the totals so that there are no gaps. This is what I have. Name Salary Tax Total Ann 42000 5000 37000 Joe 37500 6000 31500 Total 79500 11000 68500 This is what I want Name Salary Tax Total Ann 42000 5000 37000 Joe 37500 6000 31500 Total 79500 11000...

POP3 #3
I installed a new Exchange Server 2003 on a Server 2003 Server. I have an existing 2003 Exchange Server on a 2000 Domain Controller - I have moved everything over to this new Server - but when I reboot the New Server the POP3 Service fails with the following error - Has anyone seen this error before? Any resolution? Event Type: Error Event Source: Service Control Manager Event Category: None Event ID: 7023 Date: 9/20/2004 Time: 9:03:41 AM User: N/A Computer: CORONABEER Description: The Microsoft Exchange POP3 service terminated with the following error: No site name is available ...

Worksheet to .cvs format
I have worksheet that has email addresses in one column. I am trying to make this list a .vvs format to import into an email program to send an email to all my contacts. I have followed the instructions to save as .cvs format and nothing happens. Can anyone help? What do you mean "nothing happens"? Does the file not get saved? What if you save the file in a different format - does something happen then? If the file isn't getting saved, list the steps in "the instructions" you're following... In article <9C404812-0CB7-4DEC-8E24-96E777EDF784@microsoft.c...

CRM 1.2 Database on a server, and CRM 3.0 installed on a new server... how to move content?
Hi, does it possible to move the content from a CRM1.2 database to a new CRM 3.0 installation? I can move the accounts and some information using CSV files, but not the activities. what can I do? thanks. Jerome. ideally you would upgrade the original system to crm 3.0 as well then use a tool like scribe migrate to move data between the two systems ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "Jeje" <willgart@hotmail.com> wrote in message news:%23NveHq1iGHA.4344@TK2MSFTNGP05.phx.gbl... > Hi, > > does it p...

Disable changing the sort/grouping setting in inbox messages
Is there a way to disable a person from changing his sorting/grouping of messages in his inbox in Outlook 2003? We have a person that keeps sorting/grouping by sender, so he tends to only see the messages he wants to see. We want him keeping them sorted by received date/time so he sees all messages as they come in, as some are going unread for days that he needs to be seeing and doing something with. Anyway to force a sort order in messages, and disallow grouping, through a registry entry, or in some other way? Greg No. Unfortunately there are no technological solutions for this behavioral...

Concatenating two conditions in a "IF" Formula.
Hi, Can anyone advice me how to concatenate two conditions in a "IF" formula? Thanks and regards. Ringo Hi do you mean something like =IF(AND(A1=1,B1=2),"Match","no match") -- Regards Frank Kabel Frankfurt, Germany "ringo tan" <ringotan@discussions.microsoft.com> schrieb im Newsbeitrag news:A9C0093A-7C59-4F9F-A6ED-2B4CA57694DB@microsoft.com... > Hi, > > Can anyone advice me how to concatenate two conditions in a "IF" formula? > > Thanks and regards. > > > Ringo Maybe..... =IF(AND(A1=1,B1=2),CONCATENATE(A1...

Formatting text color in Outlook 2003
Is there a way to have Outlook 2003 remember my text color and use it in every email? I've changed my color several times but it still uses black as the automatic setting. I find this very frustrating since I prefer to use navy blue in all my emails but don't know how to have OL remember it Thanks! Sorry! I already found out how to accomplish this task. No need to reply. ...

Combine two sets of overlapping Date/Time data into 3 columns.
I have two sets of data with Date/Time. One set has a fixed interval, the second is more sporadic. I am trying to combine into these sets into one table with the data from one set next to the corresponding data from the second set. For example: A B C D 4/22/2010 9:00 000 4/22/2010 9:01 777 4/22/2010 9:01 111 4/22/2010 9:03 888 4/22/2010 9:02 222 4/22/2010 9:06 999 4/22/2010 9:03 333 4/22/2010 9:04 444 4/22/2010 9:05 555 4/22/2010 9:06 666 Output: A ...

Investment Summary on Home Page #3
I have a "watch list" of stocks that correctly get displayed in my "Portfolio Manager". However, on my Home Page, in the "Investment Summary" ALL my stocks are listed - the ones in my real stock accounts and the ones in my Watch List. Is it possible to NOT display in my "Investment Summary" stocks that are in my "Watch List"? Thanks, Tom ...

crm 3 web access slow
any recomendations to make the services act normaly. i use 1.5M download adsl with 96k upload. ...

Can conditional formatting be used to format chart elements?
I'm using Excel 2003 and want to conditionally format bars in a bar chart to reflect the range of values they show. Is this possible...? Hi, By using additional series and formula yes it is possible. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "socpbro" <socpbro@discussions.microsoft.com> wrote in message news:918EF2E3-092D-4277-BA6E-2D6C2CC9A359@microsoft.com... > I'm using Excel 2003 and want to conditionally format bars in a bar chart > to > reflect the range of ...

Merging Office Portal 2003 and GP BPortal 3.0
Anyone got any GOOD documentation on how to merge things using Office Portal and GP BPortal? Sharing the web parts etc. For example, I'd like to use the News list from the main portal site as the news on the BP home page, and some info/parts in the BP pages on the home page of the portal site. They are both on the same server, if that matters. I've read thru teh 'A natural fit' paper that MS puts out but it doesn't really cover much. http://portal http://portal/sites/businessportal many thanks ...

3-State CTreeCtrl
Hi, Is there a way to have a 3-state TreeCtrl without owner-drawing those rectangles? I need to have selected, not selected and semi-selected (grayed). Thanks in advance, Stilgar. Are you talking about a check boxes in the tree control, or the tree control items themselvs??? I haven't seen a multiselect tree control to begin with. AliR. "Stilgar" <stilgar@divrei-tora.com> wrote in message news:OgrSD$lrFHA.4072@TK2MSFTNGP09.phx.gbl... > Hi, > > Is there a way to have a 3-state TreeCtrl without owner-drawing those > rectangles? I need to have selected, ...

Can I set "search from start" as default search?
I am using Outlook 2000 SR-1 on a PC running Windows XP. I'm wondering if there is a way to change the default search so that Outlook searches all entries, including past ones. I need to do this all the time, and it's a pain to have to change the settings every time. Thanks, .. Joann ...

Change colour of 'hidden formatting symbols'
Hi group, is it possible to change the colur of the hidden formatting symbols so that they are contrasted to the normal text? Cheers -Ralf There is no practical way, no. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "ralf.schaa" <ralf.schaa@gmail.com> wrote in message news:4e8af37e-6dc6-4924-ad7a-7ecd98d6b6a1@j14g2000yqm.googlegroups.com... > Hi group, > > is it possible to change the colur of the hidden formatting symbols so > that they are contrasted to the normal text? > >...