[mso] Re: Conditional formatting
- From: "David Smart" <smartware@xxxxxxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Sat, 13 Feb 2010 02:31:40 +1100
With conditional formatting, what happens is that Excel stops as soon as one
of the conditions is true. I.e. if condition 1 is satisfied, then it'll
never look at condition 2 or 3.
With this sort of red, amber, green conditions, you make use of this. I.e.
test for the red condition in condition 1. Then if that isn't satisfied,
you test for the item to be at least old enough for amber. Although this
would also be satisfied for a red condition item, it'll never get the
opportunity, because condition 1 will have fired.
You can potentially do the green condition with condition 3, but it isn't
necessary. Simply set the basic colour to green. This will be overridden
by conditions 1 and 2 as needed.
So
Condition 1 - cell<today()-335 gives red
Condition 2 - cell<today()-275 gives amber
Condition 3 - not used
Cell normal background - green
If you wanted four conditions - e.g. green for 7 days old, white for less
than 7 days old, then you would use all three conditions ...
Condition 1 - cell<today()-335 gives red
Condition 2 - cell<today()-275 gives amber
Condition 3 - cell<today()-7 gives green
Cell normal background - white
Also, note that there is a problem with your range checks
cell<today()-335
and(cell<today()-275, cell>today()-335)
cell>today()-275
This will fail for the situation where the item is exactly 275 or 355 old.
For range checks, you need to use >= or <= for one side of your check, e,g,
cell<today()-335
and(cell<today()-275, cell>=today()-335)
cell>=today()-275
or
cell<=today()-335
and(cell<=today()-275, cell>today()-335)
cell>today()-275
Regards, Dave S
----- Original Message -----
From: <Jens.N.Outzen@xxxxxxxxxxxxxx>
To: <mso@xxxxxxxxxxxxx>
Sent: Friday, February 12, 2010 11:28 PM
Subject: [mso] Re: Conditional formatting
>I thought that I could do that - but excel seems to default everything
> when I do that to the max (or in this case as it should less to the min
> value) colour (in my case to red) irrespective of the date I put in.
> I love excel but there are times when it has me running in rings :-)
>
> Jens N. Outzen
>
> Lea Interchange Depot
> 0208 988 4700
> 07931 904 611 (Mob)
>
> First Capital North Limited No 0263641
> First Capital East Limited No 02218277
> Registered in England & Wales
> B Block 3rd Floor MacMillan House, Paddington Station,London W2 1TY
>
>
>
>
> From:
> "David Smart" <smartware@xxxxxxxxxxxxxxx>
> To:
> <mso@xxxxxxxxxxxxx>
> Date:
> 12/02/2010 12:14
> Subject:
> [mso] Re: Conditional formatting
> Sent by:
> mso-bounce@xxxxxxxxxxxxx
>
>
>
> Start with the furthest one in condition 1. E.g.
>
> Cond 1 value < now() - 11 months
> Cond 2 value < now() - 9 months
> Cond 3 hmmm not sure that you need a Cond 3 - it's just the default cell
>
> colour
>
> That way you don't need to check with a "between" test.
>
> Regards, Dave S
>
> ----- Original Message -----
> From: <Jens.N.Outzen@xxxxxxxxxxxxxx>
> To: <mso@xxxxxxxxxxxxx>
> Sent: Friday, February 12, 2010 10:26 PM
> Subject: [mso] Conditional formatting
>
>
>>I cannot get this to work !
>> It might be because I am using dates - but it should still work. I am
>> stuck - help please.
>>
>> I want a cell to have no colour if the date that has been entered is
> less
>> than 9 months old.
>> I want the same cell to change colour if the date is between 9 months
> and
>> 11 months old (to, say, yellow)
>> I want the same cell to change colour if the date is more than 11 months
>> old to RED.
>>
>> What I tried to do was as follows:
>>
>> Cell value between now()-275 and now()-335 format yellow
>> cell value less than now()-336 format red
>>
>> in conditional values.
>>
>> If I only have one set of values represented - no problem. I just cannot
>> seem to get two sets to work in a cell (and I should be able to do at
>> least 3).
>>
>> I am using Windows 2003
>>
>>
>>
>> Jens N. Outzen
>>
>> Lea Interchange Depot
>> 0208 988 4700
>> 07931 904 611 (Mob)
>>
>> First Capital North Limited No 0263641
>> First Capital East Limited No 02218277
>> Registered in England & Wales
>> B Block 3rd Floor MacMillan House, Paddington Station,London W2 1TY
>>
>>
>> This message is confidential. It may not be disclosed to, or used by,
>> anyone other than the addressee. If you receive this message in
>> error, please advise us immediately.
>>
>> Internet email is not necessarily secure. First does not accept
>> responsibility for changes to any email which occur after the email
>> has been sent. Attachments to this email could contain software
>> viruses which could damage your system. First have checked the
>> attachments for viruses before sending, but you should virus-check
>> them before opening.
>>
>> For more information on our range of services or to book your tickets
>> online, please visit:- http://www.firstgroup.com
>>
>>
>> *************************************************************
>> You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx
> or
>> MicrosoftOffice@xxxxxxxxxxxxxxxx
>>
>> To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
>>
>> To Unsubscribe from this group, visit the group's homepage and use the
>> dropdown menu at the top. This will allow you to unsubscribe your email
>
>> address or change your email settings to digest or vacation (no mail).
>> //www.freelists.org/webpage/mso
>>
>> To be able to share files with the group, you must join our Yahoo sister
>
>> group. This group will not allow for posting of emails, but will allow
>> you to join and share problem files, templates, etc.:
>> http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for
>> FILE SHARING ONLY.
>>
>> If you are using Outlook and you see a lot of unnecessary code in your
>> email messages, read these instructions that explain why and how to fix
>> it:
>> http://personal-computer-tutor.com/abc3/v28/greg28.htm
>> *************************************************************
>
>
> --------------------------------------------------------------------------------
>
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.435 / Virus Database: 271.1.1/2682 - Release Date: 02/11/10
> 16:09:00
>
> *************************************************************
> You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
> MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
>
> To Unsubscribe from this group, visit the group's homepage and use the
> dropdown menu at the top. This will allow you to unsubscribe your email
> address or change your email settings to digest or vacation (no mail).
> //www.freelists.org/webpage/mso
>
> To be able to share files with the group, you must join our Yahoo sister
> group. This group will not allow for posting of emails, but will allow
> you to join and share problem files, templates, etc.:
> http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for
> FILE SHARING ONLY.
>
> If you are using Outlook and you see a lot of unnecessary code in your
> email messages, read these instructions that explain why and how to fix
> it:
> http://personal-computer-tutor.com/abc3/v28/greg28.htm
> *************************************************************
>
>
>
> This message is confidential. It may not be disclosed to, or used by,
> anyone other than the addressee. If you receive this message in
> error, please advise us immediately.
>
> Internet email is not necessarily secure. First does not accept
> responsibility for changes to any email which occur after the email
> has been sent. Attachments to this email could contain software
> viruses which could damage your system. First have checked the
> attachments for viruses before sending, but you should virus-check
> them before opening.
>
> For more information on our range of services or to book your tickets
> online, please visit:- http://www.firstgroup.com
>
>
> *************************************************************
> You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
> MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
>
> To Unsubscribe from this group, visit the group's homepage and use the
> dropdown menu at the top. This will allow you to unsubscribe your email
> address or change your email settings to digest or vacation (no mail).
> //www.freelists.org/webpage/mso
>
> To be able to share files with the group, you must join our Yahoo sister
> group. This group will not allow for posting of emails, but will allow
> you to join and share problem files, templates, etc.:
> http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for
> FILE SHARING ONLY.
>
> If you are using Outlook and you see a lot of unnecessary code in your
> email messages, read these instructions that explain why and how to fix
> it:
> http://personal-computer-tutor.com/abc3/v28/greg28.htm
> *************************************************************
--------------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.435 / Virus Database: 271.1.1/2682 - Release Date: 02/11/10
16:09:00
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to share files with the group, you must join our Yahoo sister group.
This group will not allow for posting of emails, but will allow you to join
and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
Other related posts:
