Hi guys,
I’d like to color TWO cells if ONE of them has a certain kind of value.
I created an example in which I want to color cells corresponding to “check” & “perc. diff.” rows.
IF the percentage error (perc. diff.) is below 5% I want them all GREEN
WHILE if it is greater or equal to 5%, I want them RED
Unfortunately, as I’ve done in the sheet above, it ONLY works PARTIALLY
What’s wrong? Is there any other way to achieve my goal?
Just use dollar sign for row number in cell address (use absolute address of row)
(Also remove the extra letter B in the function name in the second condition - “ABS”, not “abbs”)
Ok, now it works! But why do I need the dollar sign ‘$’?
Match the formula and the range to which it applies. The range includes two cells C5 and C6, the formula uses the value of one cell C6. The relative address means that the value of cell C6 will be used to calculate the color of cell C5, and - it is important! - to calculate the color of cell C6 will be used the value of the cell with a shift, the value of cell C7. To avoid this shift, the line number must be motionless. The dollar sign does exactly that.
Ok, thus Calc doesn’t apply the same Condition for EACH cell in the range but it evaluates cell-by-cell, just like for any other “normal” cell in the sheet… One question more: what happens if I block (using $) cells in the range. e.g. instead C5:C6 → $C$5:$C$6 ???
Just try to do it and make sure nothing happens. Calc will not save this change. If this happened, this conditional format would be impossible to copy to other cells.
