CODEWITHSUNDEEP
validationgoogle-sheetsgs-conditional-formatting
Jason Churchill
Jason Churchill

Reputation: 61

Conditional formatting based on validation errors

I have a drop down box and old data in the sheet that doesn't match up to the drop down box list needs to be added to my conditional formatting so the cell stands out a bit better.

Is it possible to set the conditional formatting of a cell based on whether or not the cell has a validation error?

Upvotes: 4

Views: 6392

Answers (3)

dancingintherain
dancingintherain

Reputation: 11

This worked for me. Under conditional formatting, new rule, use formula.

=IF(OR(A2="valid data 1",A2="valid data 2"),"FALSE","TRUE")

Then set error condition.

Upvotes: 0

MR_Fixer11
MR_Fixer11

Reputation: 11

Good stuff! It took me a while but I found that this formula works and will color the cells that are not in the range AND do not have a blank cell at the first column. This is good to spot data validation errors and works better than looking for a little tiny triangle at the top right of each cell.

=AND(NOT(COUNTIF($M$3:$M$40, H3)),A3<>"")

Upvotes: 1

Akshin Jalilov
Akshin Jalilov

Reputation: 1716

It is impossible to do that the way you want, but there is a workaround.

You can use conditional formatting with a custom formula.

If you data validation is from a range, use that range. If it is from a list, create a range with that list.

For example a range on Sheet2 A1:A10 has the values that are used in data validation. You want to format the range Sheet1 B:B based on values found in that range.

First, select the column in your table(or the whole table) and colour it Red. Then use Conditional Formatting with the following custom formula to colour everything that matches White

=COUNTIF(INDIRECT("Sheet2!$A$1:$A$10"),"="&B1)

You can also add another conditional formatting to colour all the empty cells as white if you have empty space after your table

Upvotes: 2

Related Questions