Message Box Notification when cells in Column have a specific value

[mporres77]

I was wondering if anyone can help me with my message box notification.

What I want the macro to do is to check the values in column H and if it has a value that is <= 60, then to have a message box pop up with a specific message.

Then I want it to do the same thing for <= 30 with a different message if it finds that value.

The one I have now is working, however it gives me a message box for every single cell it finds with that value. Right now the sheet has 4 cells that have a zero value so I get 4 message boxes.

How can I correct the code to just give me 1 message box notification regardless of how many cells have that value?

Here is the code I have so far:
Private Sub Worksheet_Activate()
For Each cell In Range("H:H")
If cell.Value <= 60 And cell.Value <> "" Then
Range("H:H").Select

MsgBox "60 Days Until Renewal"

End If
Next
End Sub

 

[hotabae]

Well, to get one message you'd want to move the msgbox line out of the "For" loop.

Typically, Id initialize a Boolean variable to False and then toggle it if you find a cell.

Like this:

Private Sub Worksheet_Activate()

Dim foundCell As Boolean: foundCell = False

For Each cell In Range("H:H")
     If cell.Value <= 60 And cell.Value <> "" Then

          Range("H:H").Select
          foundCell = True

     End If
Next

if foundCell = True Then
     MsgBox "60 Days Until Renewal"
End If

End Sub

This will solve the "multiple messages" issue, but I definitely still see some weird stuff going on elsewhere.

For instance.... Why are you selecting Range("H:H") in every iteration of the loop?

What if there are only 20 days left... Do you still want to display "60 Days..."

Maybe something like this would fix that:

MsgBox("Less than 60 Days Until Renewal")

 

[mporres77]

That worked perfectly. Thank you for your help.

I do not understand your question about Range("H:H"). The reason I need that only that column is because it is calculating how many days are left before we need to renew contracts that expire after 180 days. I originally wanted it to notify me when I activate the sheet that there are contracts that are either 60 days or less and or 30 days or less.

The way you have it here works better.