Hi,
Is there any way i can format a cell with more than 3 conditions?
Thanks in advance
Hi,
Is there any way i can format a cell with more than 3 conditions?
Thanks in advance
I don't pretend to know what i'm doing and i'm not about to start
Depends on your conditions. Do you need more than three formats, or do
you need more than three conditions? Please post the conditions.
Hi Dave
I have four ranges:
0 to 9999
10,000 to 19,999
20,000 to 29,999
and 30,000 to 39,999
on an invoice spreadsheet. If the value of an invoice falls within one of these ranges i want it to shade the cell a different colour.
thanks
Hello penri0_0,
Apparently we have the same problem.
Maybe you can learn something from my posting here:
http://www.excelforum.com/showthread.php?t=542791
Succes!
Thanks Lucas, i'll give it a go although i've got little experience programming!
The only thing I know you can do is format the cell to your first condition
under Format Cells the conditionally format for the other three. Here is a
link posted here before but I never tried.
http://www.xldynamic.com/source/xld.....Download.html
TC
"penri0_0" wrote:
>
> Hi,
>
> Is there any way i can format a cell with more than 3 conditions?
>
> Thanks in advance
>
>
> --
> penri0_0
>
>
> ------------------------------------------------------------------------
> penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947
> View this thread: http://www.excelforum.com/showthread...hreadid=542559
>
>
If you format all the cells with a base colour before adding the conditional
formats then you will have 4 colours available
Saruman
----------------------------------------------------------------------------
---------
All Outgoing Mail Checked With Norton Antivirus 2003
----------------------------------------------------------------------------
---------
"penri0_0" <penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com> wrote in
message news:penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com...
>
> Hi,
>
> Is there any way i can format a cell with more than 3 conditions?
>
> Thanks in advance
>
>
> --
> penri0_0
>
>
> ------------------------------------------------------------------------
> penri0_0's Profile:
http://www.excelforum.com/member.php...o&userid=28947
> View this thread: http://www.excelforum.com/showthread...hreadid=542559
>
Hi Saruman,
Could you explain how to format the cell with the base colour? Not sure
what you mean?
I need four colors. I have already green, yellow, and red. I just need one
more color which would be white.
1. if it is less than 1 (100%) then green
2. if it is greater than 1 but less than 1.25 then yellow
3. if it is greater than 1.25 then red
4. if it is "NA" then white
I apply the conditional formatting to a single column that has a forumla in
each cell.
Please advise. Thanks!
Amy
"Saruman" wrote:
> If you format all the cells with a base colour before adding the conditional
> formats then you will have 4 colours available
>
> Saruman
>
> ----------------------------------------------------------------------------
> ---------
> All Outgoing Mail Checked With Norton Antivirus 2003
> ----------------------------------------------------------------------------
> ---------
>
> "penri0_0" <penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com> wrote in
> message news:penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com...
> >
> > Hi,
> >
> > Is there any way i can format a cell with more than 3 conditions?
> >
> > Thanks in advance
> >
> >
> > --
> > penri0_0
> >
> >
> > ------------------------------------------------------------------------
> > penri0_0's Profile:
> http://www.excelforum.com/member.php...o&userid=28947
> > View this thread: http://www.excelforum.com/showthread...hreadid=542559
> >
>
>
>
Amy
The fourth is the default "no color".
Gord Dibben MS Excel MVP
On Thu, 20 Jul 2006 09:55:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>Hi Saruman,
>
>Could you explain how to format the cell with the base colour? Not sure
>what you mean?
>
>I need four colors. I have already green, yellow, and red. I just need one
>more color which would be white.
>
>1. if it is less than 1 (100%) then green
>2. if it is greater than 1 but less than 1.25 then yellow
>3. if it is greater than 1.25 then red
>4. if it is "NA" then white
>
>I apply the conditional formatting to a single column that has a forumla in
>each cell.
>
>Please advise. Thanks!
>Amy
>
>"Saruman" wrote:
>
>> If you format all the cells with a base colour before adding the conditional
>> formats then you will have 4 colours available
>>
>> Saruman
>>
>> ----------------------------------------------------------------------------
>> ---------
>> All Outgoing Mail Checked With Norton Antivirus 2003
>> ----------------------------------------------------------------------------
>> ---------
>>
>> "penri0_0" <penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com> wrote in
>> message news:penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com...
>> >
>> > Hi,
>> >
>> > Is there any way i can format a cell with more than 3 conditions?
>> >
>> > Thanks in advance
>> >
>> >
>> > --
>> > penri0_0
>> >
>> >
>> > ------------------------------------------------------------------------
>> > penri0_0's Profile:
>> http://www.excelforum.com/member.php...o&userid=28947
>> > View this thread: http://www.excelforum.com/showthread...hreadid=542559
>> >
>>
>>
>>
Ok. But, how do I apply this "default" to a column that already has
conditional formatting set? Since the three conditions are already set, where
or how to tell the established formatting to us no color if it meets none of
these conditions?
Thanks for replying!
"Gord Dibben" wrote:
> Amy
>
> The fourth is the default "no color".
>
>
> Gord Dibben MS Excel MVP
>
> On Thu, 20 Jul 2006 09:55:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>
> >Hi Saruman,
> >
> >Could you explain how to format the cell with the base colour? Not sure
> >what you mean?
> >
> >I need four colors. I have already green, yellow, and red. I just need one
> >more color which would be white.
> >
> >1. if it is less than 1 (100%) then green
> >2. if it is greater than 1 but less than 1.25 then yellow
> >3. if it is greater than 1.25 then red
> >4. if it is "NA" then white
> >
> >I apply the conditional formatting to a single column that has a forumla in
> >each cell.
> >
> >Please advise. Thanks!
> >Amy
> >
> >"Saruman" wrote:
> >
> >> If you format all the cells with a base colour before adding the conditional
> >> formats then you will have 4 colours available
> >>
> >> Saruman
> >>
> >> ----------------------------------------------------------------------------
> >> ---------
> >> All Outgoing Mail Checked With Norton Antivirus 2003
> >> ----------------------------------------------------------------------------
> >> ---------
> >>
> >> "penri0_0" <penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com> wrote in
> >> message news:penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com...
> >> >
> >> > Hi,
> >> >
> >> > Is there any way i can format a cell with more than 3 conditions?
> >> >
> >> > Thanks in advance
> >> >
> >> >
> >> > --
> >> > penri0_0
> >> >
> >> >
> >> > ------------------------------------------------------------------------
> >> > penri0_0's Profile:
> >> http://www.excelforum.com/member.php...o&userid=28947
> >> > View this thread: http://www.excelforum.com/showthread...hreadid=542559
> >> >
> >>
> >>
> >>
>
>
I do not know which 3 conditions you have used, but assuming Green, Red and
Yellow for the first 3 conditions............
Just don't format if "NA"
Gord
On Thu, 20 Jul 2006 11:02:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>Ok. But, how do I apply this "default" to a column that already has
>conditional formatting set? Since the three conditions are already set, where
>or how to tell the established formatting to us no color if it meets none of
>these conditions?
>
>Thanks for replying!
>
>"Gord Dibben" wrote:
>
>> Amy
>>
>> The fourth is the default "no color".
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Thu, 20 Jul 2006 09:55:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>>
>> >Hi Saruman,
>> >
>> >Could you explain how to format the cell with the base colour? Not sure
>> >what you mean?
>> >
>> >I need four colors. I have already green, yellow, and red. I just need one
>> >more color which would be white.
>> >
>> >1. if it is less than 1 (100%) then green
>> >2. if it is greater than 1 but less than 1.25 then yellow
>> >3. if it is greater than 1.25 then red
>> >4. if it is "NA" then white
>> >
>> >I apply the conditional formatting to a single column that has a forumla in
>> >each cell.
>> >
>> >Please advise. Thanks!
>> >Amy
>> >
>> >"Saruman" wrote:
>> >
>> >> If you format all the cells with a base colour before adding the conditional
>> >> formats then you will have 4 colours available
>> >>
>> >> Saruman
>> >>
>> >> ----------------------------------------------------------------------------
>> >> ---------
>> >> All Outgoing Mail Checked With Norton Antivirus 2003
>> >> ----------------------------------------------------------------------------
>> >> ---------
>> >>
>> >> "penri0_0" <penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com> wrote in
>> >> message news:penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com...
>> >> >
>> >> > Hi,
>> >> >
>> >> > Is there any way i can format a cell with more than 3 conditions?
>> >> >
>> >> > Thanks in advance
>> >> >
>> >> >
>> >> > --
>> >> > penri0_0
>> >> >
>> >> >
>> >> > ------------------------------------------------------------------------
>> >> > penri0_0's Profile:
>> >> http://www.excelforum.com/member.php...o&userid=28947
>> >> > View this thread: http://www.excelforum.com/showthread...hreadid=542559
>> >> >
>> >>
>> >>
>> >>
>>
>>
Gord Dibben MS Excel MVP
Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value,
the cell changed to its default base color, which was none. But, when I
applied the template to a new report, for some reason when "NA" was entered,
the conditional formatting applied the red color, that was meant only for
values that were above 1.25.
Hmm. Thanks for the clarification.
"Gord Dibben" wrote:
> I do not know which 3 conditions you have used, but assuming Green, Red and
> Yellow for the first 3 conditions............
>
> Just don't format if "NA"
>
>
> Gord
>
> On Thu, 20 Jul 2006 11:02:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>
> >Ok. But, how do I apply this "default" to a column that already has
> >conditional formatting set? Since the three conditions are already set, where
> >or how to tell the established formatting to us no color if it meets none of
> >these conditions?
> >
> >Thanks for replying!
> >
> >"Gord Dibben" wrote:
> >
> >> Amy
> >>
> >> The fourth is the default "no color".
> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
> >> On Thu, 20 Jul 2006 09:55:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
> >>
> >> >Hi Saruman,
> >> >
> >> >Could you explain how to format the cell with the base colour? Not sure
> >> >what you mean?
> >> >
> >> >I need four colors. I have already green, yellow, and red. I just need one
> >> >more color which would be white.
> >> >
> >> >1. if it is less than 1 (100%) then green
> >> >2. if it is greater than 1 but less than 1.25 then yellow
> >> >3. if it is greater than 1.25 then red
> >> >4. if it is "NA" then white
> >> >
> >> >I apply the conditional formatting to a single column that has a forumla in
> >> >each cell.
> >> >
> >> >Please advise. Thanks!
> >> >Amy
> >> >
> >> >"Saruman" wrote:
> >> >
> >> >> If you format all the cells with a base colour before adding the conditional
> >> >> formats then you will have 4 colours available
> >> >>
> >> >> Saruman
> >> >>
> >> >> ----------------------------------------------------------------------------
> >> >> ---------
> >> >> All Outgoing Mail Checked With Norton Antivirus 2003
> >> >> ----------------------------------------------------------------------------
> >> >> ---------
> >> >>
> >> >> "penri0_0" <penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com> wrote in
> >> >> message news:penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com...
> >> >> >
> >> >> > Hi,
> >> >> >
> >> >> > Is there any way i can format a cell with more than 3 conditions?
> >> >> >
> >> >> > Thanks in advance
> >> >> >
> >> >> >
> >> >> > --
> >> >> > penri0_0
> >> >> >
> >> >> >
> >> >> > ------------------------------------------------------------------------
> >> >> > penri0_0's Profile:
> >> >> http://www.excelforum.com/member.php...o&userid=28947
> >> >> > View this thread: http://www.excelforum.com/showthread...hreadid=542559
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
>
> Gord Dibben MS Excel MVP
>
Happy to hear you're sorted out.
Thanks for the feedback.
On Thu, 20 Jul 2006 11:40:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value,
>the cell changed to its default base color, which was none. But, when I
>applied the template to a new report, for some reason when "NA" was entered,
>the conditional formatting applied the red color, that was meant only for
>values that were above 1.25.
>
>Hmm. Thanks for the clarification.
>
>"Gord Dibben" wrote:
>
>> I do not know which 3 conditions you have used, but assuming Green, Red and
>> Yellow for the first 3 conditions............
>>
>> Just don't format if "NA"
>>
>>
>> Gord
>>
>> On Thu, 20 Jul 2006 11:02:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>>
>> >Ok. But, how do I apply this "default" to a column that already has
>> >conditional formatting set? Since the three conditions are already set, where
>> >or how to tell the established formatting to us no color if it meets none of
>> >these conditions?
>> >
>> >Thanks for replying!
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> Amy
>> >>
>> >> The fourth is the default "no color".
>> >>
>> >>
>> >> Gord Dibben MS Excel MVP
>> >>
>> >> On Thu, 20 Jul 2006 09:55:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>> >>
>> >> >Hi Saruman,
>> >> >
>> >> >Could you explain how to format the cell with the base colour? Not sure
>> >> >what you mean?
>> >> >
>> >> >I need four colors. I have already green, yellow, and red. I just need one
>> >> >more color which would be white.
>> >> >
>> >> >1. if it is less than 1 (100%) then green
>> >> >2. if it is greater than 1 but less than 1.25 then yellow
>> >> >3. if it is greater than 1.25 then red
>> >> >4. if it is "NA" then white
>> >> >
>> >> >I apply the conditional formatting to a single column that has a forumla in
>> >> >each cell.
>> >> >
>> >> >Please advise. Thanks!
>> >> >Amy
>> >> >
>> >> >"Saruman" wrote:
>> >> >
>> >> >> If you format all the cells with a base colour before adding the conditional
>> >> >> formats then you will have 4 colours available
>> >> >>
>> >> >> Saruman
>> >> >>
>> >> >> ----------------------------------------------------------------------------
>> >> >> ---------
>> >> >> All Outgoing Mail Checked With Norton Antivirus 2003
>> >> >> ----------------------------------------------------------------------------
>> >> >> ---------
>> >> >>
>> >> >> "penri0_0" <penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com> wrote in
>> >> >> message news:penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com...
>> >> >> >
>> >> >> > Hi,
>> >> >> >
>> >> >> > Is there any way i can format a cell with more than 3 conditions?
>> >> >> >
>> >> >> > Thanks in advance
>> >> >> >
>> >> >> >
>> >> >> > --
>> >> >> > penri0_0
>> >> >> >
>> >> >> >
>> >> >> > ------------------------------------------------------------------------
>> >> >> > penri0_0's Profile:
>> >> >> http://www.excelforum.com/member.php...o&userid=28947
>> >> >> > View this thread: http://www.excelforum.com/showthread...hreadid=542559
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>>
>> Gord Dibben MS Excel MVP
>>
Gord Dibben MS Excel MVP
To clarify, the settings are as follows
condition 1: cell value is less than or equal to then green
condition 2: cell value is between 1 and 1.25 then yellow
condition 3: cell value is greater or equal to 1.25 then red
For some reason when the cell vallue is "NA" (without the quotes) condition
3 is applied.
"Amy" wrote:
> Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value,
> the cell changed to its default base color, which was none. But, when I
> applied the template to a new report, for some reason when "NA" was entered,
> the conditional formatting applied the red color, that was meant only for
> values that were above 1.25.
>
> Hmm. Thanks for the clarification.
>
> "Gord Dibben" wrote:
>
> > I do not know which 3 conditions you have used, but assuming Green, Red and
> > Yellow for the first 3 conditions............
> >
> > Just don't format if "NA"
> >
> >
> > Gord
> >
> > On Thu, 20 Jul 2006 11:02:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
> >
> > >Ok. But, how do I apply this "default" to a column that already has
> > >conditional formatting set? Since the three conditions are already set, where
> > >or how to tell the established formatting to us no color if it meets none of
> > >these conditions?
> > >
> > >Thanks for replying!
> > >
> > >"Gord Dibben" wrote:
> > >
> > >> Amy
> > >>
> > >> The fourth is the default "no color".
> > >>
> > >>
> > >> Gord Dibben MS Excel MVP
> > >>
> > >> On Thu, 20 Jul 2006 09:55:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
> > >>
> > >> >Hi Saruman,
> > >> >
> > >> >Could you explain how to format the cell with the base colour? Not sure
> > >> >what you mean?
> > >> >
> > >> >I need four colors. I have already green, yellow, and red. I just need one
> > >> >more color which would be white.
> > >> >
> > >> >1. if it is less than 1 (100%) then green
> > >> >2. if it is greater than 1 but less than 1.25 then yellow
> > >> >3. if it is greater than 1.25 then red
> > >> >4. if it is "NA" then white
> > >> >
> > >> >I apply the conditional formatting to a single column that has a forumla in
> > >> >each cell.
> > >> >
> > >> >Please advise. Thanks!
> > >> >Amy
> > >> >
> > >> >"Saruman" wrote:
> > >> >
> > >> >> If you format all the cells with a base colour before adding the conditional
> > >> >> formats then you will have 4 colours available
> > >> >>
> > >> >> Saruman
> > >> >>
> > >> >> ----------------------------------------------------------------------------
> > >> >> ---------
> > >> >> All Outgoing Mail Checked With Norton Antivirus 2003
> > >> >> ----------------------------------------------------------------------------
> > >> >> ---------
> > >> >>
> > >> >> "penri0_0" <penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com> wrote in
> > >> >> message news:penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com...
> > >> >> >
> > >> >> > Hi,
> > >> >> >
> > >> >> > Is there any way i can format a cell with more than 3 conditions?
> > >> >> >
> > >> >> > Thanks in advance
> > >> >> >
> > >> >> >
> > >> >> > --
> > >> >> > penri0_0
> > >> >> >
> > >> >> >
> > >> >> > ------------------------------------------------------------------------
> > >> >> > penri0_0's Profile:
> > >> >> http://www.excelforum.com/member.php...o&userid=28947
> > >> >> > View this thread: http://www.excelforum.com/showthread...hreadid=542559
> > >> >> >
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> >
> > Gord Dibben MS Excel MVP
> >
Actually, its not resolved. In my reply just before yours, I outlined the
setitngs that I am using. For some reason the deafult base color isn't being
applied.
A.
"Gord Dibben" wrote:
> Happy to hear you're sorted out.
>
> Thanks for the feedback.
>
> On Thu, 20 Jul 2006 11:40:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>
> >Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value,
> >the cell changed to its default base color, which was none. But, when I
> >applied the template to a new report, for some reason when "NA" was entered,
> >the conditional formatting applied the red color, that was meant only for
> >values that were above 1.25.
> >
> >Hmm. Thanks for the clarification.
> >
> >"Gord Dibben" wrote:
> >
> >> I do not know which 3 conditions you have used, but assuming Green, Red and
> >> Yellow for the first 3 conditions............
> >>
> >> Just don't format if "NA"
> >>
> >>
> >> Gord
> >>
> >> On Thu, 20 Jul 2006 11:02:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
> >>
> >> >Ok. But, how do I apply this "default" to a column that already has
> >> >conditional formatting set? Since the three conditions are already set, where
> >> >or how to tell the established formatting to us no color if it meets none of
> >> >these conditions?
> >> >
> >> >Thanks for replying!
> >> >
> >> >"Gord Dibben" wrote:
> >> >
> >> >> Amy
> >> >>
> >> >> The fourth is the default "no color".
> >> >>
> >> >>
> >> >> Gord Dibben MS Excel MVP
> >> >>
> >> >> On Thu, 20 Jul 2006 09:55:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
> >> >>
> >> >> >Hi Saruman,
> >> >> >
> >> >> >Could you explain how to format the cell with the base colour? Not sure
> >> >> >what you mean?
> >> >> >
> >> >> >I need four colors. I have already green, yellow, and red. I just need one
> >> >> >more color which would be white.
> >> >> >
> >> >> >1. if it is less than 1 (100%) then green
> >> >> >2. if it is greater than 1 but less than 1.25 then yellow
> >> >> >3. if it is greater than 1.25 then red
> >> >> >4. if it is "NA" then white
> >> >> >
> >> >> >I apply the conditional formatting to a single column that has a forumla in
> >> >> >each cell.
> >> >> >
> >> >> >Please advise. Thanks!
> >> >> >Amy
> >> >> >
> >> >> >"Saruman" wrote:
> >> >> >
> >> >> >> If you format all the cells with a base colour before adding the conditional
> >> >> >> formats then you will have 4 colours available
> >> >> >>
> >> >> >> Saruman
> >> >> >>
> >> >> >> ----------------------------------------------------------------------------
> >> >> >> ---------
> >> >> >> All Outgoing Mail Checked With Norton Antivirus 2003
> >> >> >> ----------------------------------------------------------------------------
> >> >> >> ---------
> >> >> >>
> >> >> >> "penri0_0" <penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com> wrote in
> >> >> >> message news:penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com...
> >> >> >> >
> >> >> >> > Hi,
> >> >> >> >
> >> >> >> > Is there any way i can format a cell with more than 3 conditions?
> >> >> >> >
> >> >> >> > Thanks in advance
> >> >> >> >
> >> >> >> >
> >> >> >> > --
> >> >> >> > penri0_0
> >> >> >> >
> >> >> >> >
> >> >> >> > ------------------------------------------------------------------------
> >> >> >> > penri0_0's Profile:
> >> >> >> http://www.excelforum.com/member.php...o&userid=28947
> >> >> >> > View this thread: http://www.excelforum.com/showthread...hreadid=542559
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
>
> Gord Dibben MS Excel MVP
>
How about you change condition 3 to "not equal to" NA
Anything over 1.25 will show red.
Note: condition 2 colors 1.25 yellow.
Excel seems to think between 1 and 1.25 includes 1.25
Gord
On Thu, 20 Jul 2006 11:52:01 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>To clarify, the settings are as follows
>
>condition 1: cell value is less than or equal to then green
>condition 2: cell value is between 1 and 1.25 then yellow
>condition 3: cell value is greater or equal to 1.25 then red
>
>For some reason when the cell vallue is "NA" (without the quotes) condition
>3 is applied.
>
>"Amy" wrote:
>
>> Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value,
>> the cell changed to its default base color, which was none. But, when I
>> applied the template to a new report, for some reason when "NA" was entered,
>> the conditional formatting applied the red color, that was meant only for
>> values that were above 1.25.
>>
>> Hmm. Thanks for the clarification.
>>
>> "Gord Dibben" wrote:
>>
>> > I do not know which 3 conditions you have used, but assuming Green, Red and
>> > Yellow for the first 3 conditions............
>> >
>> > Just don't format if "NA"
>> >
>> >
>> > Gord
>> >
>> > On Thu, 20 Jul 2006 11:02:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>> >
>> > >Ok. But, how do I apply this "default" to a column that already has
>> > >conditional formatting set? Since the three conditions are already set, where
>> > >or how to tell the established formatting to us no color if it meets none of
>> > >these conditions?
>> > >
>> > >Thanks for replying!
>> > >
>> > >"Gord Dibben" wrote:
>> > >
>> > >> Amy
>> > >>
>> > >> The fourth is the default "no color".
>> > >>
>> > >>
>> > >> Gord Dibben MS Excel MVP
>> > >>
>> > >> On Thu, 20 Jul 2006 09:55:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>> > >>
>> > >> >Hi Saruman,
>> > >> >
>> > >> >Could you explain how to format the cell with the base colour? Not sure
>> > >> >what you mean?
>> > >> >
>> > >> >I need four colors. I have already green, yellow, and red. I just need one
>> > >> >more color which would be white.
>> > >> >
>> > >> >1. if it is less than 1 (100%) then green
>> > >> >2. if it is greater than 1 but less than 1.25 then yellow
>> > >> >3. if it is greater than 1.25 then red
>> > >> >4. if it is "NA" then white
>> > >> >
>> > >> >I apply the conditional formatting to a single column that has a forumla in
>> > >> >each cell.
>> > >> >
>> > >> >Please advise. Thanks!
>> > >> >Amy
>> > >> >
>> > >> >"Saruman" wrote:
>> > >> >
>> > >> >> If you format all the cells with a base colour before adding the conditional
>> > >> >> formats then you will have 4 colours available
>> > >> >>
>> > >> >> Saruman
>> > >> >>
>> > >> >> ----------------------------------------------------------------------------
>> > >> >> ---------
>> > >> >> All Outgoing Mail Checked With Norton Antivirus 2003
>> > >> >> ----------------------------------------------------------------------------
>> > >> >> ---------
>> > >> >>
>> > >> >> "penri0_0" <penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com> wrote in
>> > >> >> message news:penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com...
>> > >> >> >
>> > >> >> > Hi,
>> > >> >> >
>> > >> >> > Is there any way i can format a cell with more than 3 conditions?
>> > >> >> >
>> > >> >> > Thanks in advance
>> > >> >> >
>> > >> >> >
>> > >> >> > --
>> > >> >> > penri0_0
>> > >> >> >
>> > >> >> >
>> > >> >> > ------------------------------------------------------------------------
>> > >> >> > penri0_0's Profile:
>> > >> >> http://www.excelforum.com/member.php...o&userid=28947
>> > >> >> > View this thread: http://www.excelforum.com/showthread...hreadid=542559
>> > >> >> >
>> > >> >>
>> > >> >>
>> > >> >>
>> > >>
>> > >>
>> >
>> > Gord Dibben MS Excel MVP
>> >
Gord Dibben MS Excel MVP
Didn't work.
I changed the formula to enter nothing in the cell ("0" would be misleading)
and reset the conditional formatting to
Condition 1 cell value is between .01 and 1 then green
Condition 2 cell value is between 1.01 and 1.25 then yellow
Condition 3 cell value is greater than 1.25 then red
But the empty cells are still getting red. (no pun intended!)
"Gord Dibben" wrote:
> How about you change condition 3 to "not equal to" NA
>
> Anything over 1.25 will show red.
>
> Note: condition 2 colors 1.25 yellow.
>
> Excel seems to think between 1 and 1.25 includes 1.25
>
>
> Gord
>
> On Thu, 20 Jul 2006 11:52:01 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>
> >To clarify, the settings are as follows
> >
> >condition 1: cell value is less than or equal to then green
> >condition 2: cell value is between 1 and 1.25 then yellow
> >condition 3: cell value is greater or equal to 1.25 then red
> >
> >For some reason when the cell vallue is "NA" (without the quotes) condition
> >3 is applied.
> >
> >"Amy" wrote:
> >
> >> Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value,
> >> the cell changed to its default base color, which was none. But, when I
> >> applied the template to a new report, for some reason when "NA" was entered,
> >> the conditional formatting applied the red color, that was meant only for
> >> values that were above 1.25.
> >>
> >> Hmm. Thanks for the clarification.
> >>
> >> "Gord Dibben" wrote:
> >>
> >> > I do not know which 3 conditions you have used, but assuming Green, Red and
> >> > Yellow for the first 3 conditions............
> >> >
> >> > Just don't format if "NA"
> >> >
> >> >
> >> > Gord
> >> >
> >> > On Thu, 20 Jul 2006 11:02:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
> >> >
> >> > >Ok. But, how do I apply this "default" to a column that already has
> >> > >conditional formatting set? Since the three conditions are already set, where
> >> > >or how to tell the established formatting to us no color if it meets none of
> >> > >these conditions?
> >> > >
> >> > >Thanks for replying!
> >> > >
> >> > >"Gord Dibben" wrote:
> >> > >
> >> > >> Amy
> >> > >>
> >> > >> The fourth is the default "no color".
> >> > >>
> >> > >>
> >> > >> Gord Dibben MS Excel MVP
> >> > >>
> >> > >> On Thu, 20 Jul 2006 09:55:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
> >> > >>
> >> > >> >Hi Saruman,
> >> > >> >
> >> > >> >Could you explain how to format the cell with the base colour? Not sure
> >> > >> >what you mean?
> >> > >> >
> >> > >> >I need four colors. I have already green, yellow, and red. I just need one
> >> > >> >more color which would be white.
> >> > >> >
> >> > >> >1. if it is less than 1 (100%) then green
> >> > >> >2. if it is greater than 1 but less than 1.25 then yellow
> >> > >> >3. if it is greater than 1.25 then red
> >> > >> >4. if it is "NA" then white
> >> > >> >
> >> > >> >I apply the conditional formatting to a single column that has a forumla in
> >> > >> >each cell.
> >> > >> >
> >> > >> >Please advise. Thanks!
> >> > >> >Amy
> >> > >> >
> >> > >> >"Saruman" wrote:
> >> > >> >
> >> > >> >> If you format all the cells with a base colour before adding the conditional
> >> > >> >> formats then you will have 4 colours available
> >> > >> >>
> >> > >> >> Saruman
> >> > >> >>
> >> > >> >> ----------------------------------------------------------------------------
> >> > >> >> ---------
> >> > >> >> All Outgoing Mail Checked With Norton Antivirus 2003
> >> > >> >> ----------------------------------------------------------------------------
> >> > >> >> ---------
> >> > >> >>
> >> > >> >> "penri0_0" <penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com> wrote in
> >> > >> >> message news:penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com...
> >> > >> >> >
> >> > >> >> > Hi,
> >> > >> >> >
> >> > >> >> > Is there any way i can format a cell with more than 3 conditions?
> >> > >> >> >
> >> > >> >> > Thanks in advance
> >> > >> >> >
> >> > >> >> >
> >> > >> >> > --
> >> > >> >> > penri0_0
> >> > >> >> >
> >> > >> >> >
> >> > >> >> > ------------------------------------------------------------------------
> >> > >> >> > penri0_0's Profile:
> >> > >> >> http://www.excelforum.com/member.php...o&userid=28947
> >> > >> >> > View this thread: http://www.excelforum.com/showthread...hreadid=542559
> >> > >> >> >
> >> > >> >>
> >> > >> >>
> >> > >> >>
> >> > >>
> >> > >>
> >> >
> >> > Gord Dibben MS Excel MVP
> >> >
>
> Gord Dibben MS Excel MVP
>
If the formula in the cell returns a number or "" then go with this.
1 cell value is between .01 and 1 then green
2. cell value is between 1.01 and 1.25 then yellow
3. formula is =cellref<>"" then red
Note: what color do you want if the value is less than .1
As it stands, it will be red.
I'm running out of ideas.......your parameters keep changing from one post to
the next.
Probably because I keep changing the CF entries<g>
Gord
On Thu, 20 Jul 2006 12:55:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>Didn't work.
>
>I changed the formula to enter nothing in the cell ("0" would be misleading)
>and reset the conditional formatting to
>
>Condition 1 cell value is between .01 and 1 then green
>Condition 2 cell value is between 1.01 and 1.25 then yellow
>Condition 3 cell value is greater than 1.25 then red
>
>But the empty cells are still getting red. (no pun intended!)
>
>
>
>"Gord Dibben" wrote:
>
>> How about you change condition 3 to "not equal to" NA
>>
>> Anything over 1.25 will show red.
>>
>> Note: condition 2 colors 1.25 yellow.
>>
>> Excel seems to think between 1 and 1.25 includes 1.25
>>
>>
>> Gord
>>
>> On Thu, 20 Jul 2006 11:52:01 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>>
>> >To clarify, the settings are as follows
>> >
>> >condition 1: cell value is less than or equal to then green
>> >condition 2: cell value is between 1 and 1.25 then yellow
>> >condition 3: cell value is greater or equal to 1.25 then red
>> >
>> >For some reason when the cell vallue is "NA" (without the quotes) condition
>> >3 is applied.
>> >
>> >"Amy" wrote:
>> >
>> >> Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value,
>> >> the cell changed to its default base color, which was none. But, when I
>> >> applied the template to a new report, for some reason when "NA" was entered,
>> >> the conditional formatting applied the red color, that was meant only for
>> >> values that were above 1.25.
>> >>
>> >> Hmm. Thanks for the clarification.
>> >>
>> >> "Gord Dibben" wrote:
>> >>
>> >> > I do not know which 3 conditions you have used, but assuming Green, Red and
>> >> > Yellow for the first 3 conditions............
>> >> >
>> >> > Just don't format if "NA"
>> >> >
>> >> >
>> >> > Gord
>> >> >
>> >> > On Thu, 20 Jul 2006 11:02:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>> >> >
>> >> > >Ok. But, how do I apply this "default" to a column that already has
>> >> > >conditional formatting set? Since the three conditions are already set, where
>> >> > >or how to tell the established formatting to us no color if it meets none of
>> >> > >these conditions?
>> >> > >
>> >> > >Thanks for replying!
>> >> > >
>> >> > >"Gord Dibben" wrote:
>> >> > >
>> >> > >> Amy
>> >> > >>
>> >> > >> The fourth is the default "no color".
>> >> > >>
>> >> > >>
>> >> > >> Gord Dibben MS Excel MVP
>> >> > >>
>> >> > >> On Thu, 20 Jul 2006 09:55:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>> >> > >>
>> >> > >> >Hi Saruman,
>> >> > >> >
>> >> > >> >Could you explain how to format the cell with the base colour? Not sure
>> >> > >> >what you mean?
>> >> > >> >
>> >> > >> >I need four colors. I have already green, yellow, and red. I just need one
>> >> > >> >more color which would be white.
>> >> > >> >
>> >> > >> >1. if it is less than 1 (100%) then green
>> >> > >> >2. if it is greater than 1 but less than 1.25 then yellow
>> >> > >> >3. if it is greater than 1.25 then red
>> >> > >> >4. if it is "NA" then white
>> >> > >> >
>> >> > >> >I apply the conditional formatting to a single column that has a forumla in
>> >> > >> >each cell.
>> >> > >> >
>> >> > >> >Please advise. Thanks!
>> >> > >> >Amy
>> >> > >> >
>> >> > >> >"Saruman" wrote:
>> >> > >> >
>> >> > >> >> If you format all the cells with a base colour before adding the conditional
>> >> > >> >> formats then you will have 4 colours available
>> >> > >> >>
>> >> > >> >> Saruman
>> >> > >> >>
>> >> > >> >> ----------------------------------------------------------------------------
>> >> > >> >> ---------
>> >> > >> >> All Outgoing Mail Checked With Norton Antivirus 2003
>> >> > >> >> ----------------------------------------------------------------------------
>> >> > >> >> ---------
>> >> > >> >>
>> >> > >> >> "penri0_0" <penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com> wrote in
>> >> > >> >> message news:penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com...
>> >> > >> >> >
>> >> > >> >> > Hi,
>> >> > >> >> >
>> >> > >> >> > Is there any way i can format a cell with more than 3 conditions?
>> >> > >> >> >
>> >> > >> >> > Thanks in advance
>> >> > >> >> >
>> >> > >> >> >
>> >> > >> >> > --
>> >> > >> >> > penri0_0
>> >> > >> >> >
>> >> > >> >> >
>> >> > >> >> > ------------------------------------------------------------------------
>> >> > >> >> > penri0_0's Profile:
>> >> > >> >> http://www.excelforum.com/member.php...o&userid=28947
>> >> > >> >> > View this thread: http://www.excelforum.com/showthread...hreadid=542559
>> >> > >> >> >
>> >> > >> >>
>> >> > >> >>
>> >> > >> >>
>> >> > >>
>> >> > >>
>> >> >
>> >> > Gord Dibben MS Excel MVP
>> >> >
>>
>> Gord Dibben MS Excel MVP
>>
Gord Dibben MS Excel MVP
I'll give that a try. Many thanks for your help and time, Gord.
"Gord Dibben" wrote:
> If the formula in the cell returns a number or "" then go with this.
>
> 1 cell value is between .01 and 1 then green
> 2. cell value is between 1.01 and 1.25 then yellow
> 3. formula is =cellref<>"" then red
>
> Note: what color do you want if the value is less than .1
>
> As it stands, it will be red.
>
> I'm running out of ideas.......your parameters keep changing from one post to
> the next.
>
> Probably because I keep changing the CF entries<g>
>
>
> Gord
>
> On Thu, 20 Jul 2006 12:55:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
>
> >Didn't work.
> >
> >I changed the formula to enter nothing in the cell ("0" would be misleading)
> >and reset the conditional formatting to
> >
> >Condition 1 cell value is between .01 and 1 then green
> >Condition 2 cell value is between 1.01 and 1.25 then yellow
> >Condition 3 cell value is greater than 1.25 then red
> >
> >But the empty cells are still getting red. (no pun intended!)
> >
> >
> >
> >"Gord Dibben" wrote:
> >
> >> How about you change condition 3 to "not equal to" NA
> >>
> >> Anything over 1.25 will show red.
> >>
> >> Note: condition 2 colors 1.25 yellow.
> >>
> >> Excel seems to think between 1 and 1.25 includes 1.25
> >>
> >>
> >> Gord
> >>
> >> On Thu, 20 Jul 2006 11:52:01 -0700, Amy <Amy@discussions.microsoft.com> wrote:
> >>
> >> >To clarify, the settings are as follows
> >> >
> >> >condition 1: cell value is less than or equal to then green
> >> >condition 2: cell value is between 1 and 1.25 then yellow
> >> >condition 3: cell value is greater or equal to 1.25 then red
> >> >
> >> >For some reason when the cell vallue is "NA" (without the quotes) condition
> >> >3 is applied.
> >> >
> >> >"Amy" wrote:
> >> >
> >> >> Ah. I see. Ok, I think when I adjusted the formula to add "NA" for a value,
> >> >> the cell changed to its default base color, which was none. But, when I
> >> >> applied the template to a new report, for some reason when "NA" was entered,
> >> >> the conditional formatting applied the red color, that was meant only for
> >> >> values that were above 1.25.
> >> >>
> >> >> Hmm. Thanks for the clarification.
> >> >>
> >> >> "Gord Dibben" wrote:
> >> >>
> >> >> > I do not know which 3 conditions you have used, but assuming Green, Red and
> >> >> > Yellow for the first 3 conditions............
> >> >> >
> >> >> > Just don't format if "NA"
> >> >> >
> >> >> >
> >> >> > Gord
> >> >> >
> >> >> > On Thu, 20 Jul 2006 11:02:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
> >> >> >
> >> >> > >Ok. But, how do I apply this "default" to a column that already has
> >> >> > >conditional formatting set? Since the three conditions are already set, where
> >> >> > >or how to tell the established formatting to us no color if it meets none of
> >> >> > >these conditions?
> >> >> > >
> >> >> > >Thanks for replying!
> >> >> > >
> >> >> > >"Gord Dibben" wrote:
> >> >> > >
> >> >> > >> Amy
> >> >> > >>
> >> >> > >> The fourth is the default "no color".
> >> >> > >>
> >> >> > >>
> >> >> > >> Gord Dibben MS Excel MVP
> >> >> > >>
> >> >> > >> On Thu, 20 Jul 2006 09:55:02 -0700, Amy <Amy@discussions.microsoft.com> wrote:
> >> >> > >>
> >> >> > >> >Hi Saruman,
> >> >> > >> >
> >> >> > >> >Could you explain how to format the cell with the base colour? Not sure
> >> >> > >> >what you mean?
> >> >> > >> >
> >> >> > >> >I need four colors. I have already green, yellow, and red. I just need one
> >> >> > >> >more color which would be white.
> >> >> > >> >
> >> >> > >> >1. if it is less than 1 (100%) then green
> >> >> > >> >2. if it is greater than 1 but less than 1.25 then yellow
> >> >> > >> >3. if it is greater than 1.25 then red
> >> >> > >> >4. if it is "NA" then white
> >> >> > >> >
> >> >> > >> >I apply the conditional formatting to a single column that has a forumla in
> >> >> > >> >each cell.
> >> >> > >> >
> >> >> > >> >Please advise. Thanks!
> >> >> > >> >Amy
> >> >> > >> >
> >> >> > >> >"Saruman" wrote:
> >> >> > >> >
> >> >> > >> >> If you format all the cells with a base colour before adding the conditional
> >> >> > >> >> formats then you will have 4 colours available
> >> >> > >> >>
> >> >> > >> >> Saruman
> >> >> > >> >>
> >> >> > >> >> ----------------------------------------------------------------------------
> >> >> > >> >> ---------
> >> >> > >> >> All Outgoing Mail Checked With Norton Antivirus 2003
> >> >> > >> >> ----------------------------------------------------------------------------
> >> >> > >> >> ---------
> >> >> > >> >>
> >> >> > >> >> "penri0_0" <penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com> wrote in
> >> >> > >> >> message news:penri0_0.27wqdd_1147794630.3673@excelforum-nospam.com...
> >> >> > >> >> >
> >> >> > >> >> > Hi,
> >> >> > >> >> >
> >> >> > >> >> > Is there any way i can format a cell with more than 3 conditions?
> >> >> > >> >> >
> >> >> > >> >> > Thanks in advance
> >> >> > >> >> >
> >> >> > >> >> >
> >> >> > >> >> > --
> >> >> > >> >> > penri0_0
> >> >> > >> >> >
> >> >> > >> >> >
> >> >> > >> >> > ------------------------------------------------------------------------
> >> >> > >> >> > penri0_0's Profile:
> >> >> > >> >> http://www.excelforum.com/member.php...o&userid=28947
> >> >> > >> >> > View this thread: http://www.excelforum.com/showthread...hreadid=542559
> >> >> > >> >> >
> >> >> > >> >>
> >> >> > >> >>
> >> >> > >> >>
> >> >> > >>
> >> >> > >>
> >> >> >
> >> >> > Gord Dibben MS Excel MVP
> >> >> >
> >>
> >> Gord Dibben MS Excel MVP
> >>
>
> Gord Dibben MS Excel MVP
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks