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

hmltnangel

Active Member
Joined
Aug 25, 2010
Messages
290
Office Version
  1. 365
Platform
  1. Windows
Looking to see if I can get this to run properly. I tried, but failed :ROFLMAO: 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.

VBA Code:
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
That is because you are looping through every row between 4 and 1971!

Try this to just check the selected row:
VBA Code:
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
 
Upvote 0
Solution
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)

VBA Code:
    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"
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top