I'm using the code below to lock certain cells depending on the value of other cells. The code below deals with one line of my spreadsheet only and as the spreadsheet comprises 38 data entry rows I've repeated this code 38 times in the worksheet module with the appropriate changes to row numbers.
It works, but causes much screen flickering and "thinking". I'm new to vba so no doubt I've made this code too extensive or lengthy or whatever (or just plain wrong).
Can anyone assist with suggestions on how to simplify the code and/or help with code to handle all 38 lines without repeating the routine 38 times?
Private Sub Worksheet_Change(ByVal Target As Range)
If [I11+J11=0] And [I11<>""] And [J11<>""] Then
ActiveSheet.Unprotect Password:="IPG104"
[K11:N11].Locked = True
ActiveSheet.Protect Password:="IPG104"
Else
ActiveSheet.Unprotect Password:="IPG104"
[K11:N11].Locked = False
ActiveSheet.Protect Password:="IPG104"
End If
If [E11<>""] And [I11+J11+M11=E11] Then
ActiveSheet.Unprotect Password:="IPG104"
[O11:R11].Locked = True
ActiveSheet.Protect Password:="IPG104"
Else
ActiveSheet.Unprotect Password:="IPG104"
[O11:R11].Locked = False
ActiveSheet.Protect Password:="IPG104"
End If
(all the above repeated 38 times with changes to reflect row numbers)
End Sub
Thanks.
Bookmarks