+ Reply to Thread
Results 1 to 6 of 6

If Statement

  1. #1
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    If Statement

    Hello all,

    I am looking for a way to change the color of a cell based on the result. I have tried conditional formatting. What I need is a little more complicated though.

    I am assuming that I need an "IF" formula, but I am not sure how to do it. I'm teaching myself formulas as well as VBA at the same time, and I think I am confusing myself.

    What I need is: For the cell formula to look at another cell's value and see what letter that it's value starts with. Then I need it to look at it's own value and then based on those two conditions adjust the color of the cell acordingly.

    Example: My if formula is in A1 and it is looking at C1. If C1's value starts with say a "P" then look at A1's value. If it is between "0-MONTHS" and "6-MONTHS", then change the color of the A1 cell green.

    I also already have another formula in A1. It is:
    Please Login or Register  to view this content.
    Hope I explained that well enough.

    Thank you all in advance.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Complex "IF" Statement

    With A1 the active cell the conditional formatting formula would be:

    =AND(LEFT(C1)="P",CEILING(N1,6)=6,O1="MONTHS")
    format Green

    Using N/O avoids false positive issues where a string of say "10-MONTHS" would be treated as being between >= "0-MONTHS" and <= "6-MONTHS"

  3. #3
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Complex "IF" Statement

    Quote Originally Posted by DonkeyOte View Post
    With A1 the active cell the conditional formatting formula would be:

    =AND(LEFT(C1)="P",CEILING(N1,6)=6,O1="MONTHS")
    format Green

    Using N/O avoids false positive issues where a string of say "10-MONTHS" would be treated as being between >= "0-MONTHS" and <= "6-MONTHS"
    Okay, this works good, but I have alot more options than just the three it allows me on conditional formatting. Is there a way that I can make it look at that cell and see if it has multiple letters say, "P", "S", "B"?

    And if it is either one of those then change the condition.

    Thank you for your help!!!!

  4. #4
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Complex "IF" Statement

    Quote Originally Posted by DonkeyOte View Post
    With A1 the active cell the conditional formatting formula would be:

    =AND(LEFT(C1)="P",CEILING(N1,6)=6,O1="MONTHS")
    format Green

    Using N/O avoids false positive issues where a string of say "10-MONTHS" would be treated as being between >= "0-MONTHS" and <= "6-MONTHS"
    Also, what does the "6" mean that is next to the " N1"?

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Complex "IF" Statement

    Quote Originally Posted by whatsmyname View Post
    Okay, this works good, but I have alot more options than just the three it allows me on conditional formatting. Is there a way that I can make it look at that cell and see if it has multiple letters say, "P", "S", "B"?

    And if it is either one of those then change the condition.
    The fundamental question is whether or not you require more than 4 possible formats for any given cell ?

    If the answer to this question is yes and you're running an XL version prior to 2007 then you will need to use VBA rather than Conditional Formatting. The latter as you say limited to 3 (plus standard).

    If the format for P, S & B is the same (ie an OR test) then you can modify the rule to:

    Please Login or Register  to view this content.
    or

    Please Login or Register  to view this content.
    Quote Originally Posted by whatsmyname
    Also, what does the "6" mean that is next to the " N1"?
    Check out the Help files re: CEILING function - the 6 represents the significance.
    Last edited by DonkeyOte; 07-20-2010 at 02:10 AM.

  6. #6
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Complex "IF" Statement

    Thank you so much DonkeyOte!!!!

    I did modify the formula just a hair but it worked beautifully!!!!

    Thank you so much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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