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

[Guest]

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



----------------
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" in the message pane.

http://www.microsoft.com/office/com...d7a8313&dg=microsoft.public.excel.programming

 

[Jake Marx]

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

--
Regards,

Jake Marx
www.longhead.com


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

 

[Jim Rech]

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

--
Jim
"Mat from Nancy, France" <Mat from Nancy, (e-mail address removed)>
wrote in message | 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?).
|
|
|
| ----------------
| 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" in the message pane.
|
|
http://www.microsoft.com/office/com...d7a8313&dg=microsoft.public.excel.programming

 

[Guest]

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,

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

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



----------------
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" in the message pane.

http://www.microsoft.com/office/com...d7a8313&dg=microsoft.public.excel.programming

 

[Gary Keramidas]

check the entry for tomorrow regarding formula creation, pretty cool
http://blogs.msdn.com/excel/default.aspx

 

[Gary Keramidas]

last post got deleted for some reason

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

 

[Guest]

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

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

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



----------------
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" in the message pane.

http://www.microsoft.com/office/com...d7a8313&dg=microsoft.public.excel.programming

 

[Guest]

Thank you for ur suggestion, but problem is that where can I see in excel
2003 "Conditional Formating Rule Legend" ?

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

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



----------------
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" in the message pane.

http://www.microsoft.com/office/com...d7a8313&dg=microsoft.public.excel.programming

 

[IanKR]

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.

 

[Lin Kypta]

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