+ Reply to Thread
Results 1 to 10 of 10

conditional formatting and colored cells

  1. #1
    Registered User
    Join Date
    06-23-2008
    Posts
    29

    conditional formatting and colored cells

    i've looked through several pages of past threads and found some that are similar to this, but none that are exactly the same, i swear. please don't chastise me for posting a repeat.
    i have cells C26:C30 containing percentages. these 5 percentages should add up to 100%, (or simply 1 in excel think).
    how would I write a formula in C25 that says:
    IF SUM(C26:C30)=1, turn C25 green, when SUM(C26:30) is <1<, turn C25 Red.

    How would i go about doing this? thank you very much in advance.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Select C25 and invoke Conditional Formatting

    Select Formula Is from 1st drop down and enter formula: =SUM(C26:C30)=1

    click Format and choose Green

    click Add

    Select Formula is and enter formula: SUM(C26:C30)<>1

    Click Format and choose Red

    click Ok

    Click Ok
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    06-23-2008
    Posts
    29

    conditional formatting and chaning cell color

    thanks for the super fast reply!
    however i'm not quite there yet. does it make a difference that I'm using Excel 2007? i lost you on the part about selecting "Formula Is from the first drop down menu".
    thanks again.

  4. #4
    Forum Contributor
    Join Date
    03-14-2006
    Location
    Pakistan
    Posts
    1,791

    Lightbulb

    Quote Originally Posted by henro8
    thanks for the super fast reply!
    however i'm not quite there yet. does it make a difference that I'm using Excel 2007? i lost you on the part about selecting "Formula Is from the first drop down menu".
    thanks again.
    NBVC's forumula should work for you.
    copy them from below
    for turning C25 green
    Please Login or Register  to view this content.
    for turning C25 red
    Please Login or Register  to view this content.

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Quote Originally Posted by henro8
    thanks for the super fast reply!
    however i'm not quite there yet. does it make a difference that I'm using Excel 2007? i lost you on the part about selecting "Formula Is from the first drop down menu".
    thanks again.
    I don't have 2007, but I think you have an option in the New Formatting Rule menu.. use a formula to determine which cells to format

  6. #6
    Registered User
    Join Date
    06-23-2008
    Posts
    29

    conditional formatting and changing cell color

    i'm still in the dark about this whole thing. i've attached the worksheet i'm dealing with. again, Cell C25 is the cell i'd like to change color and cells C26:C30 are the cells which will contain the percentages, currently at 0. thanks a lot for being patient and very helpful.
    sincerely,
    henry
    Attached Files Attached Files

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    See attached...

    Click on C25 and go to Conditional Formatting to check the formulas...
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-23-2008
    Posts
    29

    conditional formatting and changing cell color

    wow that worked great! thanks a lot for the help. the cell does exactly what i wanted it to, however, there is no formula to be seen. maybe this was lost in translation between excel 97-2003 and excel 2007. either way, i would really like to see that formula so if you could maybe paste it from your copy of the sheet and enter it in code in one last response it would be much appreciated. thanks again.
    sincerely,
    henry

  9. #9
    Registered User
    Join Date
    06-23-2008
    Posts
    29

    my mistake

    nevermind, i got it. thank you all very much. i see the formula and i just learned some new cool stuff in excel!

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    If you see the conditional format being applied, then it could not have got lost in the translation.... The formula is not directly in the cell, you have to invoke Conditional Formatting from the ribbon...

    see attached for 2003 version
    Attached Images Attached Images

+ 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