How to flag a dependent validation change in the same row and apply this to cells in a column?

[StormytheHornet]

Hi, hope someone can point me in the right direction please....

I have a spreadsheet with a couple of hundred rows with two columns of validation cells on every row; one of the validation cells in column N is dependent upon the other cell in column M (they are both on the same row),, These cells get completed by several users who are initially completing the spreadsheet and then revisiting and "tweaking" the primary validation field (column M) after the dependant validation cell has already been completed. So where they get tweaked, two validation fields get out of sync and I want to flag the inconsistency to the user when this happens.

If I use the below I can get a message added in the dependent cell (N2) when the primary validation cell (M2) gets amended- perfect,. However this only applies to just one row and I want to be able to apply this to all rows in the spreadsheet,. So for example if cell M50 got amended, the message "Please select from the Drop Down" would appear in N50. Whatever I try, it just doesn't work and I'm hoping someone can help me and let me know what I need to change please.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$M$2" Then
Range("N2").Value = "Please reselect from Drop Down"

End If
End Sub

 

[Peter_SSs]

Try this in place of the above code.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("M"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
      For Each c In Changed
        c.Offset(, 1).Value = "Please reselect from Drop Down"
      Next c
    Application.EnableEvents = True
  End If
End Sub

 

[StormytheHornet]

Private Sub Worksheet_Change(ByVal Target As Range) Dim Changed As Range, c As Range Set Changed = Intersect(Target, Columns("M"), Rows("2:" & Rows.Count)) If Not Changed Is Nothing Then Application.EnableEvents = False For Each c In Changed c.Offset(, 1).Value = "Please reselect from Drop Down" Next c Application.EnableEvents = True End If End Sub

That's absolutely perfect. Thank you so much Peter, you're a complete star!

I can go to bed now...it's now 01:46 this side of the world.....

Stormy

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.