Saturday, August 12, 2017

Excel 2010 Conditional Formatting for a range based on "Less Than" and "OR"



How do I use conditional formatting on a range of values such that every third cell should be filled with a particular color if the value entered in that cell is lower than values in any of the three previous cells?



My range is listed horizontally in a row between A10:FE10.



For example, if value in cell M10 is less than value present in either J10 or value in K10 or value in L10, then I want M10 to be formatted with red fill.



Similarly, if value in P10 is less than value in M10 or N10 or O10, then P10 should be filled with red and so on.




I tried selecting the range and then using the Conditional Format Rule:
Format Only Cells that Contain - Cell Value - Less Than -
"OR(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-3),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-2),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1))"



Then, I manually changed - Applies to M10, P10, S10, V10 etc.



When I tried this with just "Less than" - OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-3), it works.



The OR function is not working with the Less Than rule in Conditional Formatting.




First of all, how do I get conditional formatting for "Less Than" and "OR".



Secondly, is there are a better way to apply the rule to every third cell than to manually enter the cell references.


Answer



Building on teylyn’s answer, you could use the formula



=AND(MOD(COLUMN(),3) = 1, OR(M10



and apply it to every cell in row 10,
and it would apply only to columns A, D, G, J, M, P, S, etc.


No comments:

Post a Comment

hard drive - Leaving bad sectors in unformatted partition?

Laptop was acting really weird, and copy and seek times were really slow, so I decided to scan the hard drive surface. I have a couple hundr...