Excel Value & MsgBox

Question

我发布了一个关于这段已经被"Alex Bell"修改的代码的问题，他帮助我编写了一段代码，每当"496“值出现在这个特定的范围内时，就会出现一个MsgBox。但是由于我在这门语言方面的知识很差，有很多事情我做不到。

下一步我要做的是做同样的事情，如果值是"496"，MsgBox警告，但现在也是"800“。

那么问题是什么呢？问题是，我无法找到将这两个条件结合在一起的方法，例如，它告诉我"496“和"800”在哪里，并填充包含该特定值的两个单元格。

这可能是一个很容易解决的问题，但我还是vba的新手，当我在学校学习vb时，我们没有学到那么多。因此，期待更多的问题，从我的相关话题与vba，我正在努力学习的时候。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Not Intersect(Target, Range("G3:G500")) Is Nothing Then
For Each cell In Target

    'need clarification
    'Me.Cells(cell.Row, "496").Interior.ColorIndex = xlColorIndexNone
    'If cell.Value <> "" And cell.Value <> prevValue Then
    'Me.Cells(cell.Row, "496").Interior.ColorIndex = 3
    'End If

   If cell.Value = "496" Then
        cell.Interior.ColorIndex = 43
        MsgBox ("The row where the status is 496 is located in: " & cell.Row)
    Else
        cell.Interior.ColorIndex = xlColorIndexNone
    End If
Next cell
End If

'If Not Intersect(Target, Range("G3:G500")) Is Nothing Then
'    For Each cell In Target
'
'        If cell.Value = "800" Then
'            cell.Interior.ColorIndex = 4
'            MsgBox ("The row where the status is 800 is located in: " & cell.Row)
'        Else
'            cell.Interior.ColorIndex = xlColorIndexNone
'        End If
'    Next cell
'End If

End Sub

Answer 1

If cell.Value = "496" Or cell.Value = "800" Then
    cell.Interior.ColorIndex = 43
    MsgBox ("The row where the status is 496 or 800 is located in: " & cell.Row)
Else
    cell.Interior.ColorIndex = xlColorIndexNone
End If

或者像这样：

If cell.Value = "496" Then
    cell.Interior.ColorIndex = 43
    MsgBox ("The row where the status is 496 is located in: " & cell.Row)
ElseIf cell.Value = "800" Then
    cell.Interior.ColorIndex = 45
    MsgBox ("The row where the status is 800 is located in: " & cell.Row)
Else
    cell.Interior.ColorIndex = xlColorIndexNone
End If

如果希望进行更多的检查，可以考虑将行号存储到变量中，最后可以调用MsgBox：

Dim rowNumbers As String
rowNumbers = ""
If Not Intersect(Target, Range("G3:G500")) Is Nothing Then
  For Each cell In Target    
       If cell.Value = "496" Then
         cell.Interior.ColorIndex = 43
         rowNumbers = rowNumbers & cell.Row & " "
       ElseIf cell.Value = "800" Then
         cell.Interior.ColorIndex = 45
         rowNumbers = rowNumbers & cell.Row & " "
       Else
         cell.Interior.ColorIndex = xlColorIndexNone
       End If
  Next cell
  MsgBox ("The rows where the status is 496 or 800 is located in: " & rowNumbers)
End If