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?
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