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

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/comm...el.programming

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/...13/480599.aspx

--
Regards,

Jake Marx
www.longhead.com


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


Mat from Nancy, France wrote:
> 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/comm...el.programming

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, [email protected]>
wrote in message news:[email protected]...
| 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/comm...el.programming

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

"Jake Marx" wrote:

> 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/...13/480599.aspx
>
> --
> Regards,
>
> Jake Marx
> www.longhead.com
>
>
> [please keep replies in the newsgroup - email address unmonitored]
>
>
> Mat from Nancy, France wrote:
> > 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/comm...el.programming
>
>
>

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

--


Gary


"Jim Rech" <[email protected]> wrote in message
news:[email protected]...
> 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,
> [email protected]>
> wrote in message
> news:[email protected]...
> | 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/comm...el.programming
>
>

last post got deleted for some reason

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

--


Gary


"Jim Rech" <[email protected]> wrote in message
news:[email protected]...
> 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,
> [email protected]>
> wrote in message
> news:[email protected]...
> | 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/comm...el.programming
>
>

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





"Mat from Nancy, France" wrote:

> 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
>
> "Jake Marx" wrote:
>
> > 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/...13/480599.aspx
> >
> > --
> > Regards,
> >
> > Jake Marx
> > www.longhead.com
> >
> >
> > [please keep replies in the newsgroup - email address unmonitored]
> >
> >
> > Mat from Nancy, France wrote:
> > > 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/comm...el.programming
> >
> >
> >