# Conditional formatting not working properly [closed]

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?

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by erAck close date 2018-12-04 15:19:34.119844

Sort by » oldest newest most voted

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")

more

Ok, now it works! But why do I need the dollar sign '$'? ( 2018-12-03 23:42:28 +0200 )edit 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. ( 2018-12-04 06:56:59 +0200 )edit 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 ???

( 2018-12-04 21:54:09 +0200 )edit

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.

( 2018-12-05 07:40:58 +0200 )edit