How do I base conditional formatting on validation results in Excel?

Question

I want to change the formatting of a cell if the cell is not valid. In this case, "valid" means that the cell has failed the data validation rules.

I'm asking this question because I couldn't find the answer on SO. I eventually solved it. I'll post my answer and see if people want to comment or provide a better answer!

Answer 1

Here's a basic outline that I want to turn into a better formatted answer later this week when I have more time.

1. Create a Data Validation rule. In my case, I referenced a list of data in another workbook.
2. Turn off the alert for invalid data, we'll use the conditional formatting to show the data is invalid.
3. Add a conditional formatting option for the cells that have the data validation rule. To do this, go to Manage Rules -> New Rule, and in the formula, use =IS_VALID(CELL("row",C4), CELL("col", C4)), where C4 is the first cell you want to start entering data into.
4. Create a custom function that looks something like

this:

Public Function IS_VALID(row, column) As Boolean
IS_VALID = Not Cells(row, column).Validation.value
End Function

Finally, you can set your conditional formatting effects to whatever you want, like coloring the cell red. This answer worked for me, and I wanted to not forget to add it to SO, but don't have the time to make it all pretty right now.