Excel VBA Protected Sheet, after insert line give Error 13

[amf22333]

I need a material record sheet which i created in Excel table, and summarize with Pivot Table (for Pivot Table refreshing after entry i use VBA Code), in Sheet i make a column which is "Approved" after approving i want that specific line will become locked (i use VBA code which work properly) but issue is that for Insert Line when i creat a code (which is also working fine)

when i insert line its show Run Time Erro-13 and if i remove "refreshing code" then other thing work,

Code is below

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveWorkbook.RefreshAll

If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
ActiveSheet.unprotect "1234"
Select Case Target.Value
Case "Approved"
Range("A" & Target.Row & ":F" & Target.Row).Locked = True
Case Else
Range("A" & Target.Row & ":F" & Target.Row).Locked = False
End Select
ActiveSheet.protect "1234"


End Sub

 

[Alex Blakenburg]

Try changing your If Interect line to this:-

If Intersect(Target, Range("G:G")) Is Nothing Or Target.Count > 1 Then Exit Sub

When you are inserting a line it is selecting the whole row as the target and Target.Value = "Approved" is erroring out because Target is not a single cell. You can see that if when it error out and you enter debug mode you type into the immediate window,
"? Target.address" (without the quotes)

 

[amf22333]

Bundle of Thanks Dear Alex, with your code everything is working now

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.