+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting over multiple cells with multiple criteria validator

  1. #1
    Registered User
    Join Date
    07-18-2015
    Location
    Earth, Upper Level
    MS-Off Ver
    Office Mac 2011
    Posts
    20

    Conditional Formatting over multiple cells with multiple criteria validator

    in a previous post I got awesome help with IF formulas and now i am taking it to the next step.
    Two Issues, but lets work on issue 1 first.

    Trying to put Conditional Formatting in B5 thru D5.
    Conditional Formatting Classic Rule applies cell text formatting
    =AND ( S5 > 30 , S5 < 40000, W5 = 0)

    Issue 1
    Conditional Formatted is only showing in B5 and I have selected the range B5 thru D5
    Conditional Formatted is only showing in B5 and I have individually selected each cell and applied the Conditional Formatted

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Conditional Formatting over multiple cells with multiple criteria validator

    You need to make the columns absolute

    PHP Code: 
    =AND($S5 30,$S5 40000,$W5=0

  3. #3
    Registered User
    Join Date
    07-18-2015
    Location
    Earth, Upper Level
    MS-Off Ver
    Office Mac 2011
    Posts
    20

    Re: Conditional Formatting over multiple cells with multiple criteria validator

    Jason that was perfect and simple! Thank you.

  4. #4
    Registered User
    Join Date
    07-18-2015
    Location
    Earth, Upper Level
    MS-Off Ver
    Office Mac 2011
    Posts
    20

    Re: Conditional Formatting over multiple cells with multiple criteria validator

    Issue 2
    I am want this actually on rows 5 thru 100.
    Currently have done a copy and paste, but the Conditional Formating still shows it reference row 5, not each specific row.
    However when scrolling down to row 77, all formatting works and looks to row 77 independently, not row 5 as shown in the rules window.

    Does that make sense? Should I be worried?

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Conditional Formatting over multiple cells with multiple criteria validator

    Nothing to worry about as long as it is doing what you want it to.

    The rule is relative to the range it is applied to unless it is defined as absolute, which was the problem behind issue 1.

    In relative terms, applying your original formula to B5:D5, the rule would show as =AND ( S5 > 30 , S5 < 40000, W5 = 0) but when evaluated, that would only apply to B5,
    When you copy the rule to C5, it effectively becomes =AND ( T5 > 30 , T5 < 40000, X5 = 0) i.e. you copy the rule 1 column to the right, all of the columns in the formula move 1 to the right with it.

    By setting the columns absolute (prefixing them with $ signs) you can stop that from happening, it will always check columns S and W, no matter where you copy the rule.

    The same applies to the rows, when you copy down, the row that is evaluated follows it, so it effectively looks at 'this row', if you always want it to compare to row 5, even when you copy down, then you need to insert $ signs between the row and column.

    =AND ( S$5 > 30 , S$5 < 40000, W$5 = 0)

    You can use different variations of this to achieve the required results, search google for 'excel relative and absolute references' to find more.

  6. #6
    Registered User
    Join Date
    07-18-2015
    Location
    Earth, Upper Level
    MS-Off Ver
    Office Mac 2011
    Posts
    20

    Re: Conditional Formatting over multiple cells with multiple criteria validator

    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)

Similar Threads

  1. [SOLVED] How do I use Conditional Formatting with multiple criteria?
    By Rhoran in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-28-2013, 01:56 AM
  2. [SOLVED] Conditional Formatting based on multiple strings in multiple cells
    By lily_ruiz_06 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-27-2013, 04:23 PM
  3. Replies: 5
    Last Post: 12-28-2012, 02:06 PM
  4. Conditional formatting with multiple criteria in different cells
    By Dinghy60 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-12-2012, 08:43 AM
  5. Conditional formatting with multiple criteria
    By boxty123 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-28-2012, 09:59 PM
  6. Conditional formatting with Multiple Criteria
    By kgibson20 in forum Excel General
    Replies: 5
    Last Post: 05-21-2012, 02:00 PM
  7. Conditional Formatting, Multiple Criteria
    By Lara in forum Excel General
    Replies: 5
    Last Post: 06-27-2006, 04:55 PM
  8. Conditional Formatting with multiple criteria
    By Lara in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-26-2006, 06: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