Turn a row's background black if cell has #VALUE! - OpenOffice Calc

I'm currently playing around with formulas and made one that works perfectly for me.

Some row will get the value #VALUE! due to them being empty when imported and I am trying to make the whole rows background black (#000000) if that is the case. I can't quite find a solution for this online. What is the syntax for it?

I'll most likely set it to the whole file, so something along the lines of: If(Cell content == #VALUE! then set this row background to black)

Or maybe modify my command I have now to do that task directly:

=LEFT(TRIM(Products.D2);FIND("/";TRIM(Products.D2))-1) if output from this equals #VALUE! set row background to black?

Any help is appreciated!


You cannot use a value from an error cell, but you can use =errortype(<cell>) and match that to the error code you want to mark.

For instance, if you want to check cell A4 for error code 522, use errortype(A4)=522 for conditional formatting. To find out the error code you need, just put =errortype(A4) in a different cell.

PS: #VALUE seems to be 519, see https://wiki.documentfoundation.org/Documentation/Calc_Functions/ERRORTYPE


Create a conditional formatting rule and use the ISERROR function:

Returns TRUE if value refers to or evaluates to any error value, including #N/A, and FALSE otherwise.

The conditional formatting rule should be a formula. For example, =ISERROR($D1). It should apply to the entire table.

The images below is from Excel as I don't use OpenOffice. But the process should be close enough for you to apply them on OpenOffice.

Caveat: Note that the ISERROR will also return TRUE for other errors.

Conditional Formatting rule

Output

Result


Here is an example of @ReddyLutonadio's answer in AOO. He says that any cell will apply to the entire table in Excel, but in AOO, it is important to enter the correct cell reference.

I selected cells A1 through A7 and then entered A1 in the condition, which will then relatively apply to the other cells in the selected range.

start at A1