Hi everyone,
I would like to configure a conditional format on a cell to highlight the cell if its content raises a data validity error.
In my case, the data validity allows the user to bypass data validation if she or he wants it (and it’s something I want to keep).
What I want is to apply a style like “Error” when the cell is not valid and “Text” if it is valid.
How can I achieve this?
Thanks by advance
If my interpretation of your question is correct then this may be the start.
EDIT: I originally uploaded the wrong file - this is now the replacement
Validation2.ods (9.7 KB)
Obviously, it needs refining if I’m on the right track but not representing the data types you anticipate
Try entering anything in cell B3 - it will present the list under “Validity” D3:D9 and if you choose to continue with an item that’s not on that list you will get a highlight.
It’s just a proof of concept - that’s “Have I interpreted your thoughts correctly?” If it’s heading in the right direction, the Validity list can be concealed by any number of mechanisms. However, If I’m, a million miles away from your question I apologise.
Your conditional format keeps “Entry 1.1” uncoloured. This is because of the between operator.
The formula expression ISNA(MATCH(B3;Valid;0) would be a better choice.
Like with 80% of all spreadheet related problems, the best solution would be a database instead of a spreadsheet.
It was merely an example to demonstrate some of the potential checks; the interpretation of LO appears to be that 1.1 is between, 1 & 7 - with which I wholeheartedly agree - the CF was “Between” the two values expressed and we have no idea what the user is actually contemplating.
I felt the OP might be dismayed at being told they hadn’t provided enough information to even contemplate offering assistance when the reality was probably that the question hadn’t been fully understood, This now encourages the further refinement of the issue.
Which now gives the OP the opportunity to investigate a more sophisticated test rather than “We can’t help you”
As usual, I’ve learned 2 things today.
Or this one: COUNTIF(Valid;B3)=0 keeping in mind that Valid is just an alias for an absolute range address. Don’t confuse with the literal word “Valid” in double quotes.
Rule of thumb: All the extra operators in the cond. formatting dialog are pointless. With formula expressions you are much more flexible.
B3 equals {expression} is equivalent to B3={expression}
B3 smaller than {expression} is equivalent to B3<{expression}
…
B3 between {expression1} and {expression1} is equivalent to
AND(B3>={expression1};B3<={expression2})
plus countless options to play with functions and relative/absolute references.
Nevertheless, all this complicated stuff becomes obsolete when using a database.
Now it’s > two things 
Thanks
I’m confused, isn’t this saying B3 must be both <=&>= ∴ can be anything?
My fault
AND(B3>={expression1};B3<={expression2})