VBA - If Corresponding Cell = "X" then Msg Box

[hmltnangel]

Looking to see if I can get this to run properly. I tried, but failed The code continually runs and loops until I Ctrl + Break

Effectively I want it that if you select a cell in the range identified, and the cell in the corresponding row, column 17 = too new to rate, then msg box pops up.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'For Each cell In Range("V4:V1971")
'If Cells(cell.Row, 17).Value = "0 - Too new to rate" Then
'MsgBox "Too New To Rate: Blah Blah"
'ElseIf Cells(cell.Row, 17).Value <> "0 - Too new to rate" Then
'End If
'Next

End Sub

 

[Joe4]

That is because you are looping through every row between 4 and 1971!

Try this to just check the selected row:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   Exit if cell selected not in range V4:V1971
    If Intersect(Target, Range("V4:V1971")) Is Nothing Then Exit Sub

'   Check value in column 17
    If Cells(Target.Row, 17).Value = "0 - Too new to rate" Then
        MsgBox "Too New To Rate: Blah Blah"
    End If

End Sub

 

[hmltnangel]

Brilliant - again just that small change

Thanks Joe

 

[Joe4]

You are welcome.

 

[hmltnangel]

Dont like reopening an old thread (even one of mine) but in the off chance I thought I may as well.

I added some other bits into this (the clear contents), and got the same issue of it hitting a debug at the stage after in the code.

I added this...(note the columns changed from V to AC and from 17-30 since the original thread)

    If Intersect(Target, Range("AC4:AC2000")) Is Nothing Then Exit Sub

    If cells(Target.Row, 30).Value <> "" Then
    msg = msg & vbCrLf & vbCrLf & "Ineligible for Merit Award: " & cells(Target.Row, 30).Value
    End If
    If cells(Target.Row, 30).Value > "" Then
    Range("AC4:AC2000").ClearContents
    End If
    If msg <> "" Then MsgBox msg, Title:="Ineligible Colleague"

 

[Joe4]

Your code doesn't seem to make much sense to me.
I don't know if it is because you are leaving out important pieces of the code, or just have written it correctly.

Though it may be based on your original question, it looks to be a bit different than your original question, in which case you should really start a new thread on it.
I recommend doing the following:
- post your entire procedure
- explain (in plain English) exactly what you want to happen. It is very difficult to try to figure out what you might are trying to do from faulty code.