I have a sheet where I am applying conditional formatting to certain cells in column B to highlight the highest and lowest values with red and green, respectively. It works for the column in which I manually enter the formula, but when I try to apply the formatting to additional ranges, it doesn't work. The conditional formatting pane says it is applied to the range, but it isn't functioning as it should. My other conditional formatting rules that aren't custom work for the other columns. It's just this one. The B column is what I'm looking at.
2
-
Could you share more information so we are able to reproduce your scenario and try to find where's the issue?– MartínFeb 15 at 5:46
-
Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely.– doubleunaryFeb 15 at 8:15
Add a comment
|
1 Answer
Edit the Apply to range box to make the conditional formatting rule point to one column only, as in B1:B. Then click Add another rule to make a copy of the rule, and edit the Apply to range box, and the custom formula in the rule, to refer to another column. This way, each rule will calculate the max and min value in its "own" column.
Alternatively, put all the column references in the Apply to range box, as in B1:B,D1:D,F1:G, and use a custom formula rule that determines the "current" column, like this
=let(c, B1, c = max(indirect(regexreplace(cell("address", c), ".([A-Z]+).(\d+)", "$1:$1"))))
...where B1 is the top left cell in the first range reference in the Apply to range box.
