# Conditional Formatting over multiple cells with multiple criteria validator

1. ## 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. ## 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. ## Re: Conditional Formatting over multiple cells with multiple criteria validator

Jason that was perfect and simple! Thank you.

4. ## 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. ## 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. ## Re: Conditional Formatting over multiple cells with multiple criteria validator

Thank you so much!

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

#### 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