如何在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!

我问这个问题,因为我找不到答案。我最终解决了它。我会发布我的回答,看看有人想评论或提供更好的答案!

1 个解决方案

#1

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.

为具有数据验证规则的单元格添加条件格式设置选项。为此,请转到管理规则 - >新规则,并在公式中,使用= IS_VALID(CELL(“row”,C4),CELL(“col”,C4)),其中C4是您想要的第一个单元格开始输入数据。

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.

最后,您可以将条件格式设置效果设置为您想要的任何效果,例如将单元格着色为红色。这个答案对我有用,我想不要忘记把它添加到SO中,但是现在没有时间把它变得很好。

#1