Allow Relative References in Conditional Formatting Formulas
The ability to use formulas in conditional formatting is a great feature.
Unfortunately only absolute cell references are permitted in the formulas, and not relative cell references.
For example, I often want to apply red/yellow/green conditional formatting based on the value of an adjacent cell.
For example the formula >=2*$A$1 can be used with green formatting to highlight cells with values that are twice the value of cell A1.
What I want is the ability to use a reference like >=2*A$1 which will compare to the first cell of each column (A1, B1, C1, etc), rather than comparing everything to cell A1.
This is already supported.
To make a conditional format relative, define it on a single cell, using the relative formula that you’d like it to use (like the one in your idea, if you want it to adjust across columns).
This will format the cell on which the conditional format is applied.
Then, in the “Applies To” range in the Conditional Formatting Manager, change the range from that original cell, to the range on which you’d like the CF applied.
That will adjust (according to the referencing you’ve specified) the conditional formatting rule for each of the cells in that range.
BUT... cant use relative reference with conditional formatting using icon sets