Error 13 Mismatch when insert a new row in VBA

Hi,

i have created an vba script for me in my dropdown list to prompt user when select from it so it will hide an row, however in my business process i may need the user to add in a row in the table, this is where i encounter this issue. can someone help me figure why i got this error issue ?

Please Login or Register  to view this content.

The immediate cause of your problem is that if the user inserts a row, it is a Change event for the entire row. That is, if they insert a row at row 5, Target is $5:$5. You need to expand your test:

Please Login or Register  to view this content.

However, you have a bigger problem. If you allow the user to insert (or delete) rows, the rows to hide or unhide are no longer 17:22. You need a way to identify these rows without hardcoding the row numbers. Why do you want to hide them?

cos only the 2 selected value Donation to Charity OR DUMPING/DESTRUCTION will make the justification appear else is hidden

and i was told they can only insert row and not delete row

since in this case how can i improve my range select now since it will not be in row 19 ??

Delete/Insert rows within range H2:H999 may trigger the "change" also.
Use error trap to skip error
like this:

PHP Code: 

On Error Resume Next 


Code should be:

PHP Code: 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim valueJ As Range
Set valueJ = Range("H2:H999")
If Intersect(Target, valueJ) Is Nothing Then Exit Sub
On Error Resume Next
If Target.Value = "Dumping/Destruction" Or Target.Value = "Donation To Charity" Then
    MsgBox "You have selected: " & Target.Value
    Rows("17:22").Hidden = False
Else
    Rows("17:22").Hidden = True
End If
End Sub 


Note:
Case "Donation To Charity": different between cell datavalidation and code. Try to copy/paste to keep them the same.