vba Circle 无效数据宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13194987/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Circle Invalid-data Macro
提问by user1717622
I have a worksheet with many dependant dropdowns that are liable to having invalid data if the initial drop down is altered. I would like a simple auto-macro that would automatically run the "circle invalid data" command every time a cell change within a specified range (D8:T800) is detected.
我有一个包含许多相关下拉列表的工作表，如果更改初始下拉列表，这些下拉列表可能会包含无效数据。我想要一个简单的自动宏，它会在每次检测到指定范围 (D8:T800) 内的单元格更改时自动运行“圆圈无效数据”命令。
It sounds fairly straightforward but I am not sure how to do this.
这听起来相当简单，但我不知道如何做到这一点。
Question - as this macro will run every single time a cell is amended would this macro slow down the worksheet?
问题 - 因为这个宏会在每次修改单元格时运行，这个宏会减慢工作表的速度吗？
EDIT: Also: as this might be slow,is there a way we can run this command over a selected range?
编辑：另外：由于这可能很慢，有没有办法可以在选定的范围内运行此命令？
Your thoughts thanks.
你的想法谢谢。
回答by chris neilsen
Try this
尝试这个
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("D8:T800")) Is Nothing Then
Me.CircleInvalid
End If
End Sub
Note that CircleInvalidapplies to the whole sheet, so while this code only triggers when a cell inside D8:T800changes, allinvalid cells on the sheet will be circled
请注意，CircleInvalid适用于整个工作表，因此虽然此代码仅在内部单元格D8:T800发生更改时触发，但工作表上的所有无效单元格都将被圈出
回答by JustinJDavies
Try placing this code inside your Worksheet:
尝试将此代码放在您的工作表中：
Private Sub Worksheet_Change(ByVal Target As Range)
' Check target range has changed
If (Not Intersect(Target, Range("D8:T800")) Is Nothing) Then
' Prevent recusrive looping
Application.EnableEvents = False
' Refresh validation circles
Target.Worksheet.CircleInvalid
Application.EnableEvents = True
End If
End Sub
Note that this will NOT work if the values in those cells change due to a calculation from outside the specified range.
请注意，如果这些单元格中的值由于指定范围之外的计算而发生变化，则这将不起作用。
Also, the CircleInvalid method applies to the whole worksheet.
此外，CircleInvalid 方法适用于整个工作表。
You could try editing the code in the conditional to 'do something' if the Target is validated — this would result in you changing the format of invalid cells instead of having the red circles around them.
如果 Target 被验证，您可以尝试编辑条件中的代码以“做某事” - 这将导致您更改无效单元格的格式，而不是在它们周围放置红色圆圈。
**PSEUDO-CODE**
For each cell in Target.Range
cell.colour = bright red
Next cell
