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