Value change Alert VBA

Question

so I'm trying do to a alert where if the column is changed to "496", it gives a MsgBox with a alert, and the the alert needs to say the row where the change as taken place and that the value was altered to "496", I'm also aiming at putting the cell red (fill) .

I still didn't inserted the MsgBox, this code was given to me by a friend to help me out in a database in Excel. And my skill in excel vba is still poor so sorry about that. But I'm learning.

Option Explicit

Private prevValue As Variant

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
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
Next cell
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
prevValue = Target.Value
End Sub

Answer 1

Your business logic is a bit unclear and code snippet contains error. So, pertinent to the main question (adding alert via Message Box) the code may look like the following:

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 = 3
            MsgBox ("Cell Row=" & cell.Row)
        Else
            cell.Interior.ColorIndex = xlColorIndexNone
        End If
    Next cell
End If
End Sub

It will set the ColorIndex = 3 and display a Message Box showing Cell's Row number if any Cell value in the Range("G3:G500") was set to "496". You can further modify the prompt message: MsgBox ("Cell Row=" & cell.Row) pertinent to your business logic.

Hope it may help.