Excel VBA - Value & MsgBox

840 views Asked by At

I posted a question about this piece of code that is already changed by "Alex Bell", He helped me with the code making a MsgBox appear every time the value "496" appears in that specific range. But due to my poor knowledge in this language, there's a lot of things I cannot do.

the next step I was trying to achieve was doing the same thing that is already done, the MsgBox alert if the value is "496", but now with "800" too.

So what is the problem? The Problem is that I cannot figure a way to put the two conditions to work together, for example it tells me where is the "496" and then the "800" and fills both of the cells that contain that specific values.

It's probably a easy question to solve, but again I'm a newbie to vba and when I studied vb in school we didn't learn that much. So be expecting more questions from me on topics related to vba and I'm trying to learn it at the moment.

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
1

There are 1 answers

4
moffeltje On BEST ANSWER
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

Or like this:

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

If you would like to have more checks, you can consider to store the row numbers to print into a variable and at the very end you can call the 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