CODEWITHSUNDEEP
excelexcel-formulavba
Alkix
Alkix

Reputation: 165

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

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!

Upvotes: 6

Views: 8196

Answers (2)

Jonathan R Swift
Jonathan R Swift

Reputation: 143

If you are referencing a list in another workbook, then it is simpler to recreate the data validation rule as a conditional formatting rule.

If your conditional formatting starts at Cell A1 on Sheet1, and the list is contained from A1:A10 on Sheet2, then your conditional formatting rule would be:

=AND($A1<>0,COUNTIF(Sheet2!$A$1:$A$10,$A1)<1)

This would highlight the cell if its value does not appear in the source list, and is not blank.

Clearly, for non-list based data validation, you can come up with an equivalent formula in the same manner.

You could make things more robust by setting up a named range for the list which both the data validation and conditional formatting refer to, such that if the list range grows, you only need to edit it in one place (the Name Manager), and it will update both Data Validation and Conditional Formatting ranges.

Upvotes: 1

Alkix
Alkix

Reputation: 165

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.

Upvotes: 5

Related Questions