+ Reply to Thread
Results 1 to 19 of 19

Changing cell color from an if statement

  1. #1
    Registered User
    Join Date
    12-20-2010
    Location
    Cambridge, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    89

    Changing cell color from an if statement

    Hi everyone!
    This is an easy one for you Forum Geniuses!!

    Simply put, I'm trying to make an IF statement that If the value of this one cell is less than the other, I'd like it to turn green, if the value from that one particular cell is more, then I'd like it to turn red.


    Your help is greatly appreciated!
    Last edited by HHR; 09-27-2012 at 12:29 PM.

  2. #2
    Registered User
    Join Date
    04-20-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2013, 2010, 2007, 2003
    Posts
    99

    Re: Changing cell color from an if statement

    Use conditional formatting. "Less Than" is one of the standard "Highlight Cells Rules". In your case you'll want to use two rules- one less than and one greater than.
    SPARTAN
    Please click the * if my solution helped

  3. #3
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Changing cell color from an if statement

    Use Conditional Formatting with the ForumulaIs option. See conditional formatting in Excel Help
    Gary's Student

  4. #4
    Registered User
    Join Date
    12-20-2010
    Location
    Cambridge, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Changing cell color from an if statement

    There is no formula for this?
    I'll try your suggestion but would rather use a formula.

  5. #5
    Registered User
    Join Date
    12-20-2010
    Location
    Cambridge, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Changing cell color from an if statement

    I'm sorry, I'm very excel illiterate.

    Attached is what I'm trying to do.

    I'd like the cell color to change in D15. If the value of D15 is greater than A15, then I want it to change red, if not, green.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-20-2010
    Location
    Cambridge, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Changing cell color from an if statement

    I've tried the conditional formatting but it ONLY stays red, regardless of what rules I put.

  7. #7
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Changing cell color from an if statement

    See attached.
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,757

    Re: Changing cell color from an if statement

    You need 2 conditional formatting statements. I deleted the ones that you had in there. You can use formulas in conditional formatting but in this case you do not have to. (Formulas within cells do not ever, by themselves, affect cell formatting).

    1. New Rule> Format only cells that contain> [Cell Value] [greater than] [=A15] Format as red, OK
    2. New Rule> Format only Cells that contain> [Cell Value] [less than or equal to] [A15] Format as green, OK
    See attachment
    Attached Files Attached Files
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  9. #9
    Registered User
    Join Date
    06-28-2012
    Location
    India Pune
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Changing cell color from an if statement

    Hi HHR,
    If you want it in macro then please see attached with macro.Colour cell.xls

  10. #10
    Registered User
    Join Date
    12-20-2010
    Location
    Cambridge, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Changing cell color from an if statement

    Wonderful!! The color scheme worked and I added Yellow as well.
    Now I can't get the DIV/0 to go away - any ideas how to get this as a zero when the other cells are blank or have a zero?
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,757

    Re: Changing cell color from an if statement

    Since you're using Excel 2007, you can use the IFERROR function. In E2, for example

    =IFERROR(SUM(((C2/D2)*100)-100)/100,"")
    Does that work for you?

  12. #12
    Registered User
    Join Date
    12-20-2010
    Location
    Cambridge, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Changing cell color from an if statement

    ChemistB - it worked like a charm!! I can't thank you enough!!!!

  13. #13
    Registered User
    Join Date
    04-02-2012
    Location
    Midwest USA
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Changing cell color from an if statement

    I think an easier formula to get rid of the DIV/0 is to use =if(D2=0,0,formula), where D2 is the denominator in the formula. So if the denominator is 0 the result entered will be 0 and if the denominator is anything else then the result is the intended formula.

  14. #14
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,757

    Re: Changing cell color from an if statement

    Re: Post 13
    Guess it depends on what you are comfortable with.
    =IF(D2=0,0,Formula) extra keystrokes 11
    =IFERROR(Formula,"") extra keystrokes 12
    IFERROR Will catch any type of error, ie if the operator puts text into C2 while IF focuses on the #DIV/0 error

  15. #15
    Registered User
    Join Date
    09-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Changing cell color from an if statement

    Like HHR, I am very Excel illiterate. Is there a way to do this based on a range of cells? In the attached form, I will enter 12 values, one for each month of the year. I would like to have the worksheet automatically highlight the largest value within the range (in the example, the value entered for June). in different examples, other months may be the higher value so I will need to format each cell to compare to the rest of the values in the range. Thanks in advance for the help!
    Attached Files Attached Files

  16. #16
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,757

    Re: Changing cell color from an if statement

    Select your entire range (i.e B2:M20) Conditional Formatting > Use Formula

    =B2=MAX($B2:$M2)
    Format color as required.
    Does that help?

  17. #17
    Registered User
    Join Date
    09-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Changing cell color from an if statement

    Thank you ChemistB, took me a few minutes, but I got it working. appreciate the help!

    Now, let's see if I can take this one step farther.

    once I have identified the highest value, I would like to have row 3 show the result of the row 2 value divided by the highest value. Do you know of a formula that will perform this function? thanks again
    Last edited by jmansell; 09-27-2012 at 04:22 PM.

  18. #18
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,757

    Re: Changing cell color from an if statement

    =b2/max($b$2:$m$2) ?

  19. #19
    Registered User
    Join Date
    09-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Changing cell color from an if statement

    You are awesome! that does it. thank you again!

+ 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