none
IF A VALUE OF A CELL COMES IN A SPECIFIC RANGE IT SHOULD POPULATE A MESSAGE BOX RRS feed

  • Question

  • I HAVE 2 COLUMNS PART# AND SERAIL #

    if value of part# is any of below and value in Serial# comes in range below a message should be displayed "Rfet tech bulletin"

     Part No.

    Serial No. Range

    101648637

    415333–464947 and   655027–790686

    102200833

    666665–790800

    100055027

    763681–786863

    101938295

    766623–786586

    Tuesday, March 20, 2018 9:38 AM

All replies

  • DEEPU1980,
    re:  something

    1.  VBA or Formulas? (formulas can't display a message box)
    2.  Do all part numbers apply to all serial number ranges or
         does each part number have a unique serial number range?
    3.  The serial number ranges overlap, can they be consolidated?
    3.  Why not put the message in the adjacent column?
    4.  What have you done so far and what problems have appeared?
    5.  How much data and how arranged?
    6.  Are the Part No's text or numeric?

    • Edited by James Cone Tuesday, March 20, 2018 5:58 PM added question six
    Tuesday, March 20, 2018 5:56 PM
  • iam using the below code now and its working for new entries whenever I enter new serial# within the range it gives a message , what I need now is what all data already there in the sheet when we click that row containg the serial# within that range it should give a message "re inspect"

    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 J updated
        If Target.Column = 10 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 cellG As Range
        Dim cellJ As Range
        Dim rtnMsg As Boolean
       
        Set cellG = Cells(myRow, "G")
        Set cellJ = Cells(myRow, "J")
       
        rtnMsg = False

    '   Check amount in column C
        Select Case cellG.Value
            Case 101648637
                If ((cellJ.Value >= 415333) And (cellJ.Value <= 464947)) Or _
                    ((cellJ.Value >= 655027) And (cellJ.Value <= 790686)) Then
                    rtnMsg = True
                End If
            Case 102200833
                If (cellJ.Value >= 666665) And (cellJ.Value <= 790800) Then
                     rtnMsg = True
                End If
            Case 100055027
                If (cellJ.Value >= 763681) And (cellJ.Value <= 786863) Then
                     rtnMsg = True
                End If
            Case 101938295
                If (cellJ.Value >= 766623) And (cellJ.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

    Wednesday, March 21, 2018 4:22 AM
  • D,
    re:  Want...

    The code appears to function only if one of four numbers have been entered in column G.



    Wednesday, March 21, 2018 6:58 PM
  • its working fine now can we give a interactive message box with two butond "yes" "no"

    "re-inspect read the Bulletin"  yes   No   if click yes hyperlink , if click no exit.

    Friday, April 13, 2018 9:24 AM