VBA for running code depending on choice in a dropdown box for entire column or range

[gregnshel]

I have a "Project" spreadsheet in Excel 2016 with a variety of columns to track potential projects for our organization. Column "M" is called "Status" and all cells have a drop-down list with values such as "Submitted", "Awarded", "Declined" etc. I would like a different message box to pop up depending on the value selected from the drop-down. I have successfully implemented this for the first row with data (Row 8) for that one particular cell ("M8"), but I can't for the life of me figure out how to implement it for all cells in column M or ideally a range (M8:M500), as it won't always be the first row that we change the value in. It could be any cell in column M. I have this in a Worksheet_Change sub.

This is the code I have for cell M8:

    If Not Intersect(Target, Range("M8")) Is Nothing Then        
        Select Case Range("M8")
            Case "Submitted": MsgBox "Please upload your project to SharePoint now!"
            Case "Awarded": MsgBox "Please move to Accepted worksheet"
            Case "Declines": MsgBox "Please move to Declined worksheet"
        End Select
    End If

Any help is appreciated!

 

[MrKowz]

Welcome to the forums!

    If Not Intersect(Target, [B][COLOR="#FF0000"]Range("M:M")[/COLOR][/B]) Is Nothing Then
        Select Case [B][COLOR="#FF0000"]Target.Value[/COLOR][/B]
            Case "Submitted": MsgBox "Please upload your project to SharePoint now!"
            Case "Awarded": MsgBox "Please move to Accepted worksheet"
            Case "Declines": MsgBox "Please move to Declined worksheet"
        End Select
    End If

 

[gregnshel]

Yes! That did it. Thank you so much. I wish I would have submitted this about 2 hours ago!

 

[MrKowz]

Yes! That did it. Thank you so much. I wish I would have submitted this about 2 hours ago!

Happy it works out for you. Thank you for the feedback, and have a great day! If you're getting into VBA coding, I'm sure you'll find these forums invaluable.