VBA Alert pop up for values in a column

I'm trying to create a VBA -Alert pop up in a excel column.
In the excel sheet based on certain calculation some Growth% (column H) will be calculated and if the Growth% > 20%, a alert popup would be generated asking for the Reason Code, which needs to be put in Column I.
The code is working fine for a particular cell (say H7) but when I'm extending it for a range (say H7:H700), it's not working.
Can someone please assist me regarding this.
The code:

H7

H7:H700

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H7:H700") > 0.2 Then
MsgBox "GR% >20%, Put the reason code"
End If
End Sub


% growth Reason Code
34%
20%
18%

The updated snapshot of the excel sheet:

Now the ASM/RSM can update their forecast and automatically Growth % will be calculated in column H ...the same values will be copied in column I (as paste special) and if the Growth % > 20% , then the alert will pop up...

The code I'm using ( with kind help of JC Guidicelli):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xCell As Range, Rg As Range
On Error Resume Next
Sheets("Sheet1").Range("H7:H700").Copy
Sheets("Sheet1").Range("I7:I700").PasteSpecial xlPasteValues
Set Rg = Application.Intersect(Target, Range("I7:I700"))
If Not Rg Is Nothing Then
For Each xCell In Rg
If xCell.Value > 0.2 Then
xCell.Select
MsgBox "GR% >20%, Put the reason code"
Exit Sub
End If
Next
End If


End Sub

The issue is for the calculation of Growth% < 20% , it's working fine...but for Growth% >20%, it's throwing the pop up but getting stuck..
Could someone please assist me regarding this..

So, you have 2 columns and multiple rows and for each individual row, you need to check whether the value is > 0.2 and you need to alert the user for every row that is > 0.2. Am I understanding you correctly? ...( If you would include a sampling of the spreadsheet data in your questions, it would be much easier for people to understand what you are asking and to test possible solutions.)
– SeanW333
Jun 28 at 4:02

Worksheet_Change doesn't trigger on a change in a calculated formula's value.
– Jeeped
Jun 28 at 4:04

You need a little more research on worksheet_change. 1. disabling events 2. error control 3. handling target when target is more than a single cell. 4. what triggers a Worksheet_Change.
– Jeeped
Jun 28 at 4:07

@SeanW333....Yes ...this is exactly I want to do ...
– BMahanti
Jun 28 at 4:49

1 Answer
1

EDIT:
When you add or paste value in your selected range, the message is showing ;)
Try and let me know, it's working for me :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xCell As Range, Rg As Range
On Error Resume Next
Set Rg = Application.Intersect(Target, Range("H7:H700"))
If Not Rg Is Nothing Then
For Each xCell In Rg
If xCell.Value > 0.2 Then
xCell.Select
MsgBox "GR% >20%, Put the reason code"
Exit Sub
End If
Next
End If
End Sub


@ JC Guidicelli ......Thanks a lot for your detailed answer..Tried with the code mentioned...It's working when I'm running the macro at the end ...so suppose there are 5 cases where GR% >20%, 5 times the pop up is appearing...the code I'm trying is little different ...for the H7:H700 range , whenever I intend to put value in H7:H700 range and the value is >20% , it should throw a pop up , not at the end of the filling up..as per the code the I should encounter 5 times the pop up ...each time after the occurrence...Could you please help me regarding this ...Thanks in advance !!
– BMahanti
Jun 28 at 20:03

Look at the answer. I edited the code and let me know ;)
– JC Guidicelli
2 days ago

@ JC Guidicelli...Thank you...It's working ..!!
– BMahanti
2 days ago

Great. Glad to help you
– JC Guidicelli
2 days ago

@ JC Guidicelli...Thank you for the help....is it possible with some variant of this ...? suppose in column H7:H700 range the growth % is calculated ...I'm making a paste special in column I7:I700...and whenever the values in I7:I700, it would throw the same pop up...so basically, instead of data input if it's a calculated value , can the pop up be set...was trying with (attached the snap in the problem posted)..Sheets("Sheet1").Range("H7:H700").Copy Sheets("Sheet1").Range("I7:I700").PasteSpecial xlPasteValues...but it's not working...Please let me know if it's possible ...Thanks a ton..!!
– BMahanti
9 hours ago

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.