Limit message from MsgBox only to when related cells change (not any cell))

[ScottBob214]

Hello, everyone. This is my first post! And I'm also brand spanking new to VBA, so bear with me.

First, I'll explain what I'm trying to do. I manage a work order system in Excel and I want to pop up a message to the users inputting new work requests that alerts them if their requested completion date for the work order is too soon compared to our estimated completion date based on typical lead time for that work.

In this sheet, there are 3 columns where the users input information: work Order submission date (column E), estimated hours to complete (column F), and requested completion date (column G). Based on the submission date, estimated hours, and the backlog in our system, I calculate our actual predicted completion date (column H). So if the date in column G is earlier than the date in column H, I want Excel to pop up a message that warns the users that we are unlikely to meet their requested target date. (FYI a single new work order is populated within a single row.)

Here is the code I have so far:
Public Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("e84:G84")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
If Range("g84").Value < Range("h84").Value Then
MsgBox "My message here."
End If
End If
End Sub

(In case you're curious, row 84 was the next blank row I've been tinkering in to get the code to work.)

At first I wasn't using the "KeyCells" command, but the problem I had was that I would change some data in a row unrelated to the work order in row 84, and it would throw the message every time I changed the worksheet. So then I added the "KeyCells" command, and that worked well to limit Excel to throw up the message to only when the information related to the work order in row 84 was changed.

So now with all that background information, I think I can get to my actual question... How do I get this code to apply to all the rows in Excel (well, except for row 1 which has header information), but simultaneously limit Excel to run the "If Range" routine and displaying the warning message only for the row for which info was edited?

For example, let's say the work order in row 84 has a requested date that is not attainable (e.g. g84<h84). and="" then="" let's="" say="" there's="" a="" new="" work="" order="" entered="" into="" row="" 85="" that="" has="" requested="" date="" is="" attainable="" (g85="">h85), so I do NOT need any warning message to the user. I want to ensure that if somebody changes or enters that new information in row 85, a message is not shown about the unrelated work order in row 84.

Does that make sense? I've tried to provide as much detail as possible, but hopefully that wasn't information overload.</h84).>

 

[sijpie]

Hi, you're close but your logic needs a bit of refining.
You want to do the check anytime someone edits or adds a date in column G.
So in that case you compare the Target with column G. If the target is on G you do the check for the row of the target.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    ' First check to see if there is only one cell changed, _
      else this will cause an error
    If Target.Cells.Count > 1 Then Exit Sub
    
    ' Check to see if changed cell is on column G
    If Not Application.Intersect(Target, Columns("G")) Is Nothing Then
        ' compare the value of changed cell (target) with the value _
          in column H, which is on cell to the righ (offset (0,1)
        If Target.Value < Target.Offset(0, 1).Value Then
            ' Date not achievable
            MsgBox "My message here."
        End If
    End If
    
End Sub

Oh, by the way: if you post a piece of code, then use code 'brackets' around your code. See the example in my signature. Your code then stands out and keeps its indentations.

if people would paste in several dates at the same time I could amend this code so it works for each of the pasted cells.

 

[sijpie]

Oops I made a mistake: the code should be in the Worksheet_Change() sub, not the Worksheet_selectionChange() sub as shown above.

So correct is:

Private Sub Worksheet_Change(ByVal Target As Range)    
    ' First check to see if there is only one cell changed, _
      else this will cause an error
    If Target.Cells.Count > 1 Then Exit Sub
    
    ' Check to see if changed cell is on column G
    If Not Application.Intersect(Target, Columns("G")) Is Nothing Then
        ' compare the value of changed cell (target) with the value _
          in column H, which is on cell to the righ (offset (0,1)
        If Target.Value < Target.Offset(0, 1).Value Then
            ' Date not achievable
            MsgBox "My message here."
        End If
    End If


End Sub

Sorry for the confusion.

Remember this code is in the Worksheet module, not a standard macro module.

 

[ScottBob214]

Thanks!! I'll give that code a try when I'm back in the office. Thanks again for your help.