VB routine to produce message box if value greater then another

[PaulBass]

Hi All

I'm new to the site. Hopefully I have posted this properly!

In the file I'm working in, there are a series of cell into which the user can enter a value of their choice. These cells are all independent of each other so the value are also separate and independent of each other.

I wish for an message box to appear on the screen when a value in entered into a cell which is greater than the cell next to it. This I have managed. However I've been caught out by the fact there are a series of these cell. If the value in one cell is left above the threshold value every time a new number is entered the same message box appears. Even when the newly entered value is below the threshold value.

I'm not sure how to modify the below sub routine to accommodate this. I only want the message box to appear when the value entered into the cell at that time is greater than the threshold. Not the situation when one value higher up is above the threshold an then triggers the message.

Can someone advise how I can modify my coding?

Best Regards

Paul


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("L9") > Range("K9") Then MsgBox num & "Offered discount is greater than the permissible discount"
If Range("L12") > Range("K12") Then MsgBox num & "Offered discount is greater than the permissible discount. Obtain Regional Commercial Director's approval"
If Range("L16") > Range("K16") Then MsgBox num & "Offered discount is greater than the permissible discount. Obtain Regional Commercial Director's approval"
If Range("L21") > Range("K21") Then MsgBox num & "Offered discount is greater than the permissible discount. Obtain Regional Commercial Director's approval"
If Range("L24") > Range("K24") Then MsgBox num & "Offered discount is greater than the permissible discount. Obtain Regional Commercial Director's approval"
If Range("L28") > Range("K28") Then MsgBox num & "Offered discount is greater than the permissible discount. Obtain Regional Commercial Director's approval"
If Range("L32") > Range("K32") Then MsgBox num & "Offered discount is greater than the permissible discount. Obtain Regional Commercial Director's approval"
If Range("L35") > Range("K35") Then MsgBox num & "Offered discount is greater than the permissible discount. Obtain Regional Commercial Director's approval"
If Range("L38") > Range("K38") Then MsgBox num & "Offered discount is greater than the permissible discount. Obtain Regional Commercial Director's approval"
If Range("L41") > Range("K41") Then MsgBox num & "Offered discount is greater than the permissible discount. Obtain Regional Commercial Director's approval"
If Range("L46") > Range("K46") Then MsgBox num & "Offered discount is greater than the permissible discount. Obtain Regional Commercial Director's approval"
If Range("L49") > Range("K49") Then MsgBox num & "Offered discount is greater than the permissible discount. Obtain Regional Commercial Director's approval"
If Range("L51") > Range("K51") Then MsgBox num & "Offered discount is greater than the permissible discount. Obtain Regional Commercial Director's approval"
End Sub

 

[Joe4]

Welcome to the Board!

You can control this by editing your code to only run when certain cells are updated, and to only evaluate the cell being updated (and not everything in your list, every time).
So which column are you entering these values in that should trigger the code to run? Column K, L, or both?

 

[PaulBass]

Hi Joe.

Id like it to only evaluate the cell being updated and not everything in your list every time. The user entered values go into the "L" cells. Please note the "L" cells will not necessarily be updated in order or all of then at any one time.

Also it is only the "L" cells I have listed above in my code which are of interest.

Can you advise how I can update the code to achieve what you suggested?

Cheers

Paul

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim isect As Range
    Dim cell As Range
    
'   Set range to check
    Set myRange = Range("L9,L12,L16,L21,L24,L28,L32,L35,L38,L41,L46,L49,L51")
    
'   Check to see if updated cell falls in designate range
    Set isect = Intersect(Target, myRange)
'   If not, exit sub
    If isect Is Nothing Then Exit Sub
    
'   Loop through update cells in intersection (usually just one cell)
    For Each cell In isect
        If cell > cell.Offset(0, -1) Then MsgBox "Offered discount is greater than the permissible discount. Obtain Regional Commercial Director's approval"
    Next cell

End Sub

 

[PaulBass]

Hi Joe

I'm sorry but that code seems to have stopped the msgBox from appearing all together.

Is there something missing in the code or an error in it somewhere? My not familiar with the isect or Target functions. Does Target also need to be Dim'ed?

 

[PaulBass]

Hi Joe.

My error. Didn't type out the code properly. Next time I'll copy and paste.

Works perfectly!!

Thank you very much. Most appreciated.

 

[Joe4]

You are welcome!