I have this code.
However, if I protect sheet 6 (or indeed sheet 10) then the code crashes with error code 'Runtime error 1004 - Unable to set the Hidden property of the range class.
I need to unprotect sheet 6 & 10 and then protect them again when the loop has finished.
Help!
Code:
Sub Worksheet_change(ByVal Target As Range)
ActiveSheet.Unprotect
Dim i As Integer
For i = 17 To 43 Step 2
Sheet6.Rows(i).EntireRow.Hidden = Sheet5.Range("M" & i).Value = "Yes" 'Unmet needs
Sheet6.Rows(i + 1).EntireRow.Hidden = Sheet5.Range("M" & i).Value = "Yes" 'Unmet need
Sheet10.Rows(i + 17).EntireRow.Hidden = Sheet5.Range("M" & i).Value = "Yes" 'checklist
Sheet10.Rows(i + 18).EntireRow.Hidden = Sheet5.Range("M" & i).Value = "Yes" 'Checklist
Sheet6.Range("L" & i).ClearContents
Sheet6.Range("N" & i).ClearContents
Next i
ActiveSheet.protect
End Sub
However, if I protect sheet 6 (or indeed sheet 10) then the code crashes with error code 'Runtime error 1004 - Unable to set the Hidden property of the range class.
I need to unprotect sheet 6 & 10 and then protect them again when the loop has finished.
Help!