populate message if some specific number comes in a column

[deepu1980]

i have an excel with 10 columns and 26000 rows out of which column C is "part#" and F "Serial#"

if C ="101648637 " and F= value between range "415333–464947 " display message "re-inspect"
if C="101648637" and F= value between range "655027-790686 " display message "re-inspect"
if C="102200833" and F= value between range "666665-790800 " display message "re-inspect"
if C="100055027" and F= value between range "763681-786863 " display message "re-inspect"
if C="101938295 and F= value between range "766623-786586 " display message "re-inspect"

 

[Joe4]

Welcome to the Board!

Are the values entered in columns C and F entered as Text (usually left-justified) or Numbers (right-justified)?
Since you want to check for a range, Numeric entry would work better.

 

[deepu1980]

Welcome to the Board!

Are the values entered in columns C and F entered as Text (usually left-justified) or Numbers (right-justified)?
Since you want to check for a range, Numeric entry would work better.

First of all thanks for your reply yes both column contains numeric values and one more thing right now both columns already have values and when clicking these rows containing values message should pop out and also while entering these rande values in new rows in column Seial#

 

[deepu1980]

message should pop up while new entry is done in column F for the above mentioned part# in column C

 

[Joe4]

Right-click on the sheet tab name at the bottom of your sheet, select View Code, and paste all this VBA code in the resulting VB Editor Window:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run if one single cell updated
    If Target.Count > 1 Then Exit Sub
    
'   Only run if column F updated
    If Target.Column = 6 Then
        Call MyVerify(Target.Row)
    End If

End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   Only run on first row in selected range (in case multiple rows selected simultaenously)
    Call MyVerify(Target(1, 1).Row)

End Sub



Private Sub MyVerify(myRow As Long)

    Dim cellC As Range
    Dim cellF As Range
    Dim rtnMsg As Boolean
    
    Set cellC = Cells(myRow, "C")
    Set cellF = Cells(myRow, "F")
    
    rtnMsg = False

'   Check amount in column C
    Select Case cellC.Value
        Case 101648637
            If ((cellF.Value >= 415333) And (cellF.Value <= 464947)) Or _
                ((cellF.Value >= 655027) And (cellF.Value <= 790686)) Then
                rtnMsg = True
            End If
        Case 102200833
            If (cellF.Value >= 666665) And (cellF.Value <= 790800) Then
                 rtnMsg = True
            End If
        Case 100055027
            If (cellF.Value >= 763681) And (cellF.Value <= 786863) Then
                 rtnMsg = True
            End If
        Case 101938295
            If (cellF.Value >= 766623) And (cellF.Value <= 786586) Then
                 rtnMsg = True
            End If
    End Select
    
'   Return message of one of conditions is met
    If rtnMsg Then MsgBox "re-inspect"
                
End Sub

This should work on updating the value in column F, or selecting any row (it only applies to the top row in your selection, if you select multiple rows simultaneously).

 

[deepu1980]

joe,

 

[deepu1980]

joe, thanks a lot this code works excellent one more doubt my file already has existing data with part numbers and serial # coming within the range can we give a warning message "reinspect" when someone clicks this row containg the serial# within the range

 

[Joe4]

my file already has existing data with part numbers and serial # coming within the range can we give a warning message "reinspect" when someone clicks this row containg the serial# within the range

If you copied ALL of my code, it should already do that. Note that there are actually 3 different procedures in my code.
MyVerify - this is the actual code that does the checking
Worksheet_Change - this is the code that is automatically triggered when column F is updated; it calls the MyVerify code
Worksheet_SelectionChange - this is the code that is automatically triggered when a row is selected; it calls the MyVerify code

Is it not working like that? If not, please lay out a specific example for us that is not working (how you are selecting the row, what the values in columns C and F of that particular row are).

 

[deepu1980]

its working fine now , can we give an interactive message box like below

"re-inspect check the new Bulletin , do you want to read vbyesNo button and while clicking "yes" hyperlink and "no" exit

 

[Joe4]

its working fine now , can we give an interactive message box like below

"re-inspect check the new Bulletin , do you want to read vbyesNo button and while clicking "yes" hyperlink and "no" exit

I am not quite clear on what exactly you want to do in each case, but here is structure that shows you how you can do different things based on whether they select "Yes" or "No" in the MsgBox. You should be able to incorporate this logic into your code:

    Dim msg As Variant
'   Return message of one of conditions is met
    If rtnMsg Then
        msg = MsgBox("re-inspect", vbYesNo, "Message")
        Select Case msg
            Case vbYes
                'Do something here
            Case vbNo
                'Do something else here
        End Select
    End If