+ Reply to Thread
Results 1 to 11 of 11

Multiple conditions in One rule of Conditional Formatting

  1. #1
    Registered User
    Join Date
    08-13-2014
    Location
    Mumbai
    MS-Off Ver
    MS Office 2010
    Posts
    4

    Multiple conditions in One rule of Conditional Formatting

    Hello All

    I'm using MS Excel 2010 and have a query on conditional formatting.
    I have a worksheet, for which I want to set a conditional formatting rule obeying the following conditions-
    -find minimum value between cell A2 and B2, format the cell C2 as
    Cond 1- If minimum of the compared value in A2 and B2 is greater than equal to 80 but less than equal to 100, fill C2 as green.
    Cond 2- If minimum of the compared value in A2 and B2 is greater then equal to 60 but less than equal to 79, fill C2 as orange.
    Cond 1- If minimum of the compared value in A2 and B2 is greater then equal to 50 but less than equal to 59, fill C2 as yellow.
    Cond 1- If minimum of the compared value in A2 and B2 is less than equal to 49, fill C2 as red.

    Also, how to copy this rule for all the rows below.
    Kindly refer the attached file for more.
    Attached Files Attached Files

  2. #2
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Multiple conditions in One rule of Conditional Formatting

    Try this...
    Put the Minimum of A2 and B2 in C2

    in CF Formulas..
    Condition 1st Green
    =And(C2>=80,C2<=100)

    Condition 2nd Orange
    =And(C2>=60,C2<=79)

    Condition 3rd Yellow
    =And(C2>=50,C2<=59)

    Condition 4th Red
    =And(C2<=49)

    Select the cell C2 and apply above formulas in conditional formatting..

    Don't forget to click *

  3. #3
    Registered User
    Join Date
    07-22-2014
    Location
    Riyadh
    MS-Off Ver
    2010
    Posts
    88

    Re: Multiple conditions in One rule of Conditional Formatting

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-13-2014
    Location
    Mumbai
    MS-Off Ver
    MS Office 2010
    Posts
    4

    Re: Multiple conditions in One rule of Conditional Formatting

    Thanks guys for your replies.
    But i don't want to find the Minimum and put it in C2!!
    Can it be defined in the rule of CF itself?
    Also,how should i apply the same CF for all the rows below, WITHOUT defining them again and again for C2,C3,C4 and so on....

  5. #5
    Registered User
    Join Date
    07-22-2014
    Location
    Riyadh
    MS-Off Ver
    2010
    Posts
    88

    Re: Multiple conditions in One rule of Conditional Formatting

    please check attachment, select cell and drag it down

  6. #6
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,655

    Re: Multiple conditions in One rule of Conditional Formatting

    Or take a look at this.
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  7. #7
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Multiple conditions in One rule of Conditional Formatting

    Try this...

    in CF Formulas..
    Condition 1st Green
    =And(MIN(A2<B2)>=80,MIN(A2<B2)<=100,C2="")

    Condition 2nd Orange
    =And(MIN(A2<B2)>=60,MIN(A2<B2)<=79,C2="")

    Condition 3rd Yellow
    =And(MIN(A2<B2)>=50,MIN(A2<B2)<=59,C2="")

    Condition 4th Red
    =And(MIN(A2<B2)<=49,C2="")


    Select the Range from C2 to as down as you want and apply above formulas in conditional formatting..

    Don't forget to click *

  8. #8
    Registered User
    Join Date
    08-13-2014
    Location
    Mumbai
    MS-Off Ver
    MS Office 2010
    Posts
    4

    Re: Multiple conditions in One rule of Conditional Formatting

    It worked only half way. Please take a look at attachment. The comparable values are in column G and H and the result has to be displayed in cell R.
    This rule has to be applied till row number 22.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-22-2014
    Location
    Riyadh
    MS-Off Ver
    2010
    Posts
    88

    Re: Multiple conditions in One rule of Conditional Formatting

    please find attached
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-13-2014
    Location
    Mumbai
    MS-Off Ver
    MS Office 2010
    Posts
    4

    Re: Multiple conditions in One rule of Conditional Formatting

    Thank you....this works perfectly fine.

  11. #11
    Registered User
    Join Date
    07-22-2014
    Location
    Riyadh
    MS-Off Ver
    2010
    Posts
    88

    Re: Multiple conditions in One rule of Conditional Formatting

    hi

    Previous file there are some formula error please fine correct one

    thanks
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Multiple Rule Conditional Formatting
    By jbenkov in forum Excel General
    Replies: 8
    Last Post: 11-30-2012, 12:21 PM
  2. Excel 2007 : Conditional Formatting: Multiple conditions
    By mike_braden in forum Excel General
    Replies: 6
    Last Post: 03-02-2011, 07:56 PM
  3. Replies: 2
    Last Post: 09-18-2009, 07:07 PM
  4. Multiple Criteria per conditional formatting rule
    By Jonathan78 in forum Excel General
    Replies: 13
    Last Post: 09-18-2009, 05:50 PM
  5. Conditional Formatting with multiple conditions
    By Tink in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 06-11-2009, 01:37 PM

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