Worksheet_Change for a Range?

  • Thread starter Thread starter bw
  • Start date Start date
B

bw

Column B (or a range within Column B) has a Data Validation List, that can
be one of 4 values (Test1, Test2, Test3, Test4).
When I change a cell in column B, I want to know which Row has been changed.
The following works when I change Row 2. How do I make it work for the
specific cell that was changed?

Thanks,
Bernie

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim C, R
C = 2
R = 2
If ActiveSheet.Cells(R, C).Value = "" Or
IsEmpty(ActiveSheet.Cells(R, C).Value) Then
MsgBox "Delete the User Template"
Else
If ActiveSheet.Cells(R, C).Value = "Test1" Then ' I'll change this
to check for all values in the list later
MsgBox "Set the User Template"
End If
End If
End Sub
 
Bernie

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
MsgBox "The cell in row " & Target.Row & " is now: " & Target.Value
End Sub

HTH,
Bernie
MS Excel MVP
 
Back
Top