error-handling - VBA:在#VALUE错误上格式化单元格

Question

我创建了一个函数，该函数在范围内搜索特定值并返回相邻特征。

Function Busca(valor As String)
    Dim bus(0 To 1)
    bus(0) = Worksheets("Sheet2").Range("A1:A10").Find(valor, LookAt:=xlWhole). _
        Offset(0, 1)
    bus(1) = Worksheets("Sheet2").Range("A1:A10").Find(valor, LookAt:=xlWhole). _
        Offset(0, 2)
    Busca = bus
End Function

如果没有匹配 valor在 A1:A10 , 函数返回 {#VALUE,#VALUE} ，没关系，但我想返回 {"No match", ""} 之类的东西, 给定颜色的“不匹配”单元格。我已经尝试过数据验证、错误处理和 If Then与 ActiveCell.Interior.ColorIndex无济于事。另外，如果错误得到纠正，我希望单元格恢复透明。

我想事件处理程序可以解决问题，但我是 VBA 的新手，还有很多我不明白的东西。

编辑

正如建议的那样，要清楚:

如果未找到匹配项，如何使输出单元格更改颜色，如果找到匹配项(在 VBA 中)则没有颜色？

Answer 1

根据提问者的新信息，这是一个完全重写的答案。

如果我理解正确，您希望创建一个自定义函数来更改单元格的颜色。 Microsoft 的自定义函数帮助中的以下文本表明您不能:

The number of VBA keywords you can use in custom functions is smaller than the number you can use in macros. Custom functions are not allowed to do anything other than return a value to a formula in a worksheet or to an expression used in another VBA macro or function. For example, custom functions cannot resize windows, edit a formula in a cell, or change the font, color, or pattern options for the text in a cell. If you include "action" code of this kind in a function procedure, the function returns the #VALUE! error.

我知道两种可能满足您要求的替代方案。在灵光一现之后，后来又添加了第三种选择。

备选方案 1:提供允许值的下拉列表

将光标定位到具有限制值的单元格。从工具栏中选择数据，然后选择验证。将显示一个数据验证表单。

如果尚未选择，请选择设置选项卡。单击“允许:”下的框，然后选择列表。在源框中输入:“=$A$1:$A$10”。 (“=”是必需的。如果您希望能够创建此单元格的副本，则 $s 很重要。)单击“确定”。

当用户将光标定位到该单元格时，他们可以输入允许的值或从列表中选择允许的值。尝试输入任何其他值将导致错误消息。数据验证表单中的其他选项卡允许您输入帮助消息和您自己的错误消息。

由于源框中的 $s，您可以将原始单元格及其验证复制到其他单元格。

备选方案 2:条件格式

如果允许的值是一个范围，则条件格式将满足您的要求。例如，假设允许的范围是 10 到 20。

从工具栏中选择格式，然后选择条件格式。

显示条件 1 的框。 “Between”已经显示。在右侧的框中输入 10 和 20。

单击添加以显示条件 2 的框。将“之间”替换为“小于”。在下一个框中输入 10。单击“格式”。单击“颜色”。选择“红色”。单击“确定”。

单击“添加”以显示条件 3 的框。将“之间”替换为“大于”。在下一个框中输入 20。单击“格式”。单击“颜色”。选择“红色”。单击“确定”。

单击确定接受条件格式。

用户可以在单元格中输入他们喜欢的任何内容，但除非在 10 到 20 之间，否则它将是红色的。

您可以根据需要创建任意数量的格式化单元格副本。

工作表更改事件

我之前应该想到事件。我相信这正是你想要的。

在 VBA 编辑器中，项目资源管理器通常在屏幕左侧可见。如果不是，请点击 Ctrl+R .

右键单击要控制选定值的工作表的行。点击 View Code .

代码区域将以工作表为标题，否则将为空白。您可以在该区域放置各种例程，但相关例程是工作表事件例程。每当发生诸如工作表激活或停用之类的事件时，将调用这些例程。您想要的事件是 Change ，每当用户更改单元格时都会调用它。这个程序的吸引力在于它可以做任何你想做的事情。

将下面的 Worksheet_Change 例程复制并粘贴到工作表代码区域中。

它的参数是用户更改的单元格的地址。

TgtRngList 设置为您希望巡逻的范围列表。我已将其设置为 C1:C1000、F1:F1000 和 A1。您必须将其更改为您要巡逻的范围。

OKValueList 设置为巡查范围的允许值列表。它们可能在某个地方的工作表中，但我认为在这里定义它们更容易。将列表更改为您想要的任何内容。

代码检查更改的单元格是否位于巡逻区域之一。如果是，则检查它是否具有允许的值。该检查的结果导致单元格设置为黑色或红色。

Option Explicit
Sub Worksheet_Change(ByVal ChangedCell As Range)

  ' This routine is called whenever the user changes a cell.
  ' It is not called if a cell is changed by Calculate

  Dim ColChanged As Integer
  Dim InxOV As Integer
  Dim InxTR As Integer
  Dim OKValueList() As Variant
  Dim Patrolled As Boolean
  Dim RowChanged As Integer
  Dim TgtColLeft As Integer
  Dim TgtColRight As Integer
  Dim TgtRngPartList() As String
  Dim TgtRngList() As Variant
  Dim TgtRngPart As String
  Dim TgtRowBottom As Integer
  Dim TgtRowTop As Integer
  Dim ValueChanged As String
  Dim ValueOK As Boolean

  ' Fill TgtRngList withe ranges that are to be patrolled by this routine
  TgtRngList = Array("C1:C1000", "F1:F1000", "A1")

  ' Fill OKValueList with the permitted values for these cells.
  OKValueList = Array("V1", "V2", "V3", "V4", "V5", _
                      "V6", "V7", "V8", "V9", "V10")

  ColChanged = ChangedCell.Column
  RowChanged = ChangedCell.Row

  Patrolled = False
  For InxTR = LBound(TgtRngList) To UBound(TgtRngList)
    TgtRngPartList = Split(TgtRngList(InxTR), ":")
    ' Decode top left of range
    TgtRngPart = TgtRngPartList(LBound(TgtRngPartList))
    TgtRowTop = Range(TgtRngPart).Row
    TgtColLeft = Range(TgtRngPart).Column
    If LBound(TgtRngPartList) = UBound(TgtRngPartList) Then
      ' There is no colon so single cell range
      TgtRowBottom = TgtRowTop
      TgtColRight = TgtColLeft
    Else
      TgtRngPart = TgtRngPartList(UBound(TgtRngPartList))
      TgtRowBottom = Range(TgtRngPart).Row
      TgtColRight = Range(TgtRngPart).Column
    End If
    If RowChanged >= TgtRowTop And RowChanged <= TgtRowBottom And _
       ColChanged >= TgtColLeft And ColChanged <= TgtColRight Then
      ' This is a patrolled cell
      Patrolled = True
      Exit For
    End If
  Next
  If Patrolled Then
    With ActiveSheet
      ValueChanged = .Cells(RowChanged, ColChanged).Value
      ' Check value against permitted list
      ValueOK = False
      For InxOV = LBound(OKValueList) To UBound(OKValueList)
        If ValueChanged = OKValueList(InxOV) Then
          ValueOK = True
          Exit For
        End If
      Next
      If ValueOK Then
        ' Set cell black
        .Cells(RowChanged, ColChanged).Font.Color = RGB(0, 0, 0)
      Else
        ' Set cell red
        .Cells(RowChanged, ColChanged).Font.Color = RGB(255, 0, 0)
      End If
    End With
  End If

End Sub

希望这可以帮助。