How do I base conditional formatting on validation results in Excel?
excel, excel-formula, vba
Solution
Here's a basic outline that I want to turn into a better formatted answer later this week when I have more time.
- Create a Data Validation rule. In my case, I referenced a list of data in another workbook.
- Turn off the alert for invalid data, we'll use the conditional formatting to show the data is invalid.
- 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.
- 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.
Problem
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!