Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / Programming / November 2008

Tip: Looking for answers? Try searching our database.

Excel: only THREE conditions for conditional formating of cells???

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mat from Nancy, France - 19 Oct 2005 20:45 GMT
Excel is a tremendously powerful application. Why the miserly, or at least
seemingly arbitrary, restriction to three (magic number?) conditional formats
for a given cell or series of cells?

I am a university lecturer/administrator at the Université de Nancy 2 in
France and use Excel amongst other things to record details for incoming
admissions candidates for a vocational English course. The admissions
procedure includes a test whose result determines whether candidates are
refused, or allowed into various different level groups. I would like the
column which shows the decision to automatically display "refused" in red,
"abandon" in red italics, "accepted advanced level" in blue, "accepted
intermediate level" in green, and so on. Why can I only program three
conditions and not more (or can I, and I just haven't figured out how to?).
Reply to this Message
Jake Marx - 19 Oct 2005 21:13 GMT
Mat,

This has been a limitation of Conditional Formatting since it was released.
Microsoft has apparently listened to end users' frustrations with this
limitation (among others), and will be seriously revamping CF in Excel 12:

   http://blogs.msdn.com/excel/archive/2005/10/13/480599.aspx

Signature

Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

> Excel is a tremendously powerful application. Why the miserly, or at
> least seemingly arbitrary, restriction to three (magic number?)
[quoted text clipped - 19 lines]
>
> http://www.microsoft.com/office/community/en-us/default.mspx?mid=248909b0-854d-4
518-b0a0-7ccf2d7a8313&dg=microsoft.public.excel.programming 
Reply to this Message
Mat from Nancy, France - 19 Oct 2005 21:38 GMT
Thanks Jake. It's nice to hear that other users have been as annoyed by this
as I have, and that Microsoft have taken that into account. Thanks also for
the link, it was interesting reading. (Just when I was so pleased with myself
for having finally got round to buying Office 2003...;-) )

Best regards,

Matthew Smith
Université de Nancy 2, France

> Mat,
>
[quoted text clipped - 27 lines]
> >
> > http://www.microsoft.com/office/community/en-us/default.mspx?mid=248909b0-854d-4
518-b0a0-7ccf2d7a8313&dg=microsoft.public.excel.programming 
Reply to this Message
Vic Eldridge - 21 Oct 2005 03:59 GMT
Hi Nancy,

If you can't wait for the new version of Excel, you could use some code like
this to apply your own formats. To set this up, right-click on the sheet tab
then click the View Code button.  Paste the following code into the
worksheet's code window. It will now automatically apply your formatting to
any data entered in cells A1:A500.

Regards,
Vic Eldridge

Private Sub Worksheet_Change(ByVal Target As Range)
   
   Dim cel As Range
   Dim FormatRange As Range
   
   Set FormatRange = Range("A1:A500")
   
   If Not Application.Intersect(Target, FormatRange) Is Nothing Then
       
       For Each cel In FormatRange
           
           Select Case cel.Value
               Case Is = "refused"
                   cel.Font.Color = RGB(255, 0, 0)
                   cel.Font.Italic = False
               Case Is = "abandon"
                   cel.Font.Color = RGB(255, 0, 0)
                   cel.Font.Italic = True
               Case Is = "accepted advanced level"
                   cel.Font.Color = RGB(0, 0, 255)
                   cel.Font.Italic = False
               Case Is = "accepted intermediate level"
                   cel.Font.Color = RGB(0, 255, 0)
                   cel.Font.Italic = False
               Case Else
                   cel.Font.Color = RGB(0, 0, 0)
                   cel.Font.Italic = False
           End Select
           
       Next cel
       
   End If
End Sub

> Thanks Jake. It's nice to hear that other users have been as annoyed by this
> as I have, and that Microsoft have taken that into account. Thanks also for
[quoted text clipped - 37 lines]
> > >
> > > http://www.microsoft.com/office/community/en-us/default.mspx?mid=248909b0-854d-4
518-b0a0-7ccf2d7a8313&dg=microsoft.public.excel.programming 
Reply to this Message
Montu - 17 Nov 2007 05:29 GMT
Thank you for ur suggestion, but problem is that where can I see in excel
2003 "Conditional Formating Rule Legend" ?

> Mat,
>
[quoted text clipped - 27 lines]
> >
> > http://www.microsoft.com/office/community/en-us/default.mspx?mid=248909b0-854d-4
518-b0a0-7ccf2d7a8313&dg=microsoft.public.excel.programming 
Reply to this Message
IanKR - 17 Nov 2007 08:44 GMT
> Thank you for ur suggestion, but problem is that where can I see in excel
> 2003 "Conditional Formating Rule Legend" ?

You won't - only in Excel 2007. That's what "Excel 12" is.
Reply to this Message
Jim Rech - 19 Oct 2005 21:18 GMT
You should be delighted with Excel 12 as the number of conditions will be
limited only by memory.

This and many other changes are discussed in David Gainer's XL12 blog:

http://blogs.msdn.com/excel/default.aspx

Signature

Jim

| Excel is a tremendously powerful application. Why the miserly, or at least
| seemingly arbitrary, restriction to three (magic number?) conditional formats
[quoted text clipped - 16 lines]
| link to open the suggestion in the Microsoft Web-based Newsreader and then
| click "I Agree" in the message pane.

http://www.microsoft.com/office/community/en-us/default.mspx?mid=248909b0-854d-4
518-b0a0-7ccf2d7a8313&dg=microsoft.public.excel.programming
Reply to this Message
Gary Keramidas - 20 Oct 2005 03:48 GMT
check the entry for tomorrow regarding formula creation, pretty cool
http://blogs.msdn.com/excel/default.aspx

Signature

Gary

> You should be delighted with Excel 12 as the number of conditions will be
> limited only by memory.
[quoted text clipped - 33 lines]
> |
> http://www.microsoft.com/office/community/en-us/default.mspx?mid=248909b0-854d-4
518-b0a0-7ccf2d7a8313&dg=microsoft.public.excel.programming
Reply to this Message
Gary Keramidas - 20 Oct 2005 03:52 GMT
last post got deleted for some reason

check the entry for tomorrow regarding formula creation, pretty cool at the
blog site below

Signature

Gary

> You should be delighted with Excel 12 as the number of conditions will be
> limited only by memory.
[quoted text clipped - 33 lines]
> |
> http://www.microsoft.com/office/community/en-us/default.mspx?mid=248909b0-854d-4
518-b0a0-7ccf2d7a8313&dg=microsoft.public.excel.programming
Reply to this Message
Lin Kypta - 07 Nov 2008 22:02 GMT
Use nested "IF" command.  In excel 2003, you can nest IF to a depth of 7.  In
excel 2007, I found a help file that said we could nest to a depth of 64 ...
example below   IF(condx=1,blue,IF(condx=2,red,IF(condx=3,green,none)))  
to a depth of 3 ... I have used depth of 7 in excel 2003 - works ok
Lin

> Excel is a tremendously powerful application. Why the miserly, or at least
> seemingly arbitrary, restriction to three (magic number?) conditional formats
[quoted text clipped - 18 lines]
>
> http://www.microsoft.com/office/community/en-us/default.mspx?mid=248909b0-854d-4
518-b0a0-7ccf2d7a8313&dg=microsoft.public.excel.programming
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.