+ Reply to Thread
Results 1 to 5 of 5

Multiple condition formating

  1. #1
    Registered User
    Join Date
    01-15-2013
    Location
    Moncton
    MS-Off Ver
    Excel 2016
    Posts
    95

    Multiple condition formating

    I'm trying to do conditional formatting based on multiple conditions. I would like to have cell C4 turn green if any of these conditions are meet. D61=0 and D4>0 or E61=0 and E4>0.R61=0 and R4>0(for every column from D to R). If possible I would like for cell C4 turn red instead of green if more than one of these are true. I have tried a few different formulas and the closest I came would only turn C4 green if the D61=0 and D4>0 was true. Anything else didn't change the color. I know I could make multiple rules for cell C4 but would rater have 1 rule with a formula if possible.

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Multiple condition formating

    I think you're not going to get away from having two different tests, one for each color; conditional formatting by formula can only apply one format, not a range.

    Anyway, since you've got a stack of OR(AND) all nested in a row, you can actually use addition with SUMPRODUCT on your logical conditions instead of just nesting a load of boolean functions.

    Format where this formula is true to turn a cell "green" when exactly one pair meets the criteria:
    Please Login or Register  to view this content.
    Put this conditional above it to change the cell to "red" when more than one pair meets the criteria:
    Please Login or Register  to view this content.
    So that one will go higher in the list, and click the checkbox "Stop if True" in the "Conditional Formatting Rules Manager" dialogue box.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  3. #3
    Registered User
    Join Date
    01-15-2013
    Location
    Moncton
    MS-Off Ver
    Excel 2016
    Posts
    95

    Re: Multiple condition formating

    Thank you. I had to do a slight modification to your formula but it seems to work.I still have a lot of testing to do because I want to apply this to other cells than C4 but it will be the same logic so should work.I will report back after more testing. Thanks again and her is what I had to do.

    To turn the cell green
    HTML Code: 
    =SUMPRODUCT( --(D61:R61 = 0), --(D10:R10 > 0)) = 1
    To turn the cell red

    HTML Code: 
    =SUMPRODUCT( --(D61:R61 = 0), --(D10:R10 > 0)) = 1

  4. #4
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Multiple condition formating

    Those are the exact same formula, aren't they? Won't it just apply both conditional formats and just keep whichever one happened to be applied last?

  5. #5
    Registered User
    Join Date
    01-15-2013
    Location
    Moncton
    MS-Off Ver
    Excel 2016
    Posts
    95

    Re: Multiple condition formating

    Opps. paste the wrong code i used for red.
    HTML Code: 
    =SUMPRODUCT( --($D$61:$R$61 = 0), --(D10:R10 > 0)) > 1

+ 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. Condition formating
    By virencm in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-30-2014, 08:42 PM
  2. CONDITIONAL FORMATING for more then one condition
    By rajeshturaha in forum Excel General
    Replies: 1
    Last Post: 07-30-2013, 09:07 AM
  3. Condition formating a cell in a worksheet based off formating of another cell
    By bdberger1985 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-07-2013, 05:52 PM
  4. Condition Formating
    By Hlowmaster in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2010, 04:31 PM
  5. Multiple condition, multiple range formating
    By sa02000 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-11-2009, 09:10 AM
  6. [SOLVED] condition formating I think
    By Dreamstar_1961 in forum Excel General
    Replies: 3
    Last Post: 05-05-2006, 12:35 PM
  7. condition formating
    By chiuinggum in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-01-2006, 08:00 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