+ Reply to Thread
Results 1 to 19 of 19

Conditional Formatting - more than 3?

  1. #1
    Registered User
    Join Date
    11-21-2005
    Posts
    32

    Conditional Formatting - more than 3?

    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

  2. #2
    Dave O
    Guest

    Re: Conditional Formatting - more than 3?

    Depends on your conditions. Do you need more than three formats, or do
    you need more than three conditions? Please post the conditions.


  3. #3
    Registered User
    Join Date
    11-21-2005
    Posts
    32
    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

  4. #4
    Registered User
    Join Date
    05-17-2006
    Posts
    9
    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!

  5. #5
    Registered User
    Join Date
    11-21-2005
    Posts
    32
    Thanks Lucas, i'll give it a go although i've got little experience programming!

  6. #6
    TC
    Guest

    RE: Conditional Formatting - more than 3?

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


  7. #7
    Saruman
    Guest

    Re: Conditional Formatting - more than 3?

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  8. #8
    Amy
    Guest

    Re: Conditional Formatting - more than 3?

    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" <[email protected]> wrote in
    > message news:[email protected]...
    > >
    > > 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
    > >

    >
    >
    >


  9. #9
    Gord Dibben
    Guest

    Re: Conditional Formatting - more than 3?

    Amy

    The fourth is the default "no color".


    Gord Dibben MS Excel MVP

    On Thu, 20 Jul 2006 09:55:02 -0700, Amy <[email protected]> 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" <[email protected]> wrote in
    >> message news:[email protected]...
    >> >
    >> > 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
    >> >

    >>
    >>
    >>



  10. #10
    Amy
    Guest

    Re: Conditional Formatting - more than 3?

    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 <[email protected]> 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" <[email protected]> wrote in
    > >> message news:[email protected]...
    > >> >
    > >> > 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
    > >> >
    > >>
    > >>
    > >>

    >
    >


  11. #11
    Gord Dibben
    Guest

    Re: Conditional Formatting - more than 3?

    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 <[email protected]> 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 <[email protected]> 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" <[email protected]> wrote in
    >> >> message news:[email protected]...
    >> >> >
    >> >> > 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

  12. #12
    Amy
    Guest

    Re: Conditional Formatting - more than 3?

    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 <[email protected]> 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 <[email protected]> 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" <[email protected]> wrote in
    > >> >> message news:[email protected]...
    > >> >> >
    > >> >> > 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
    >


  13. #13
    Gord Dibben
    Guest

    Re: Conditional Formatting - more than 3?

    Happy to hear you're sorted out.

    Thanks for the feedback.

    On Thu, 20 Jul 2006 11:40:02 -0700, Amy <[email protected]> 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 <[email protected]> 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 <[email protected]> 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" <[email protected]> wrote in
    >> >> >> message news:[email protected]...
    >> >> >> >
    >> >> >> > 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

  14. #14
    Amy
    Guest

    Re: Conditional Formatting - more than 3?

    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 <[email protected]> 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 <[email protected]> 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" <[email protected]> wrote in
    > > >> >> message news:[email protected]...
    > > >> >> >
    > > >> >> > 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
    > >


  15. #15
    Amy
    Guest

    Re: Conditional Formatting - more than 3?

    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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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" <[email protected]> wrote in
    > >> >> >> message news:[email protected]...
    > >> >> >> >
    > >> >> >> > 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
    >


  16. #16
    Gord Dibben
    Guest

    Re: Conditional Formatting - more than 3?

    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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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" <[email protected]> wrote in
    >> > >> >> message news:[email protected]...
    >> > >> >> >
    >> > >> >> > 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

  17. #17
    Amy
    Guest

    Re: Conditional Formatting - more than 3?

    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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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" <[email protected]> wrote in
    > >> > >> >> message news:[email protected]...
    > >> > >> >> >
    > >> > >> >> > 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
    >


  18. #18
    Gord Dibben
    Guest

    Re: Conditional Formatting - more than 3?

    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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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" <[email protected]> wrote in
    >> >> > >> >> message news:[email protected]...
    >> >> > >> >> >
    >> >> > >> >> > 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

  19. #19
    Amy
    Guest

    Re: Conditional Formatting - more than 3?

    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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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" <[email protected]> wrote in
    > >> >> > >> >> message news:[email protected]...
    > >> >> > >> >> >
    > >> >> > >> >> > 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
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1