Shorten code

[Juriemagic]

Hi good people!

I have a lengthy code and Excel thinks a bit while executing. I'm trying to get the downtime from 3 sec down to at least 1 sec. Could this code be re-written so that it would execute a bit faster?. Please help...

If Not Range("D3") Is Nothing Then
Sheets("Update Room").Unprotect
 If Range("AI1") = 3 Then
 Range("I8").Locked = True
 Else
 Range("I8").Locked = False
 End If
  If Range("AI2") = 3 Then
 Range("I10").Locked = True
 Else
 Range("I10").Locked = False
 End If
If Range("AI3") = 3 Then
 Range("I12").Locked = True
 Else
 Range("I12").Locked = False
 End If
  If Range("AI4") = 3 Then
 Range("K8").Locked = True
 Else
 Range("K8").Locked = False
 End If
If Range("AI5") = 3 Then
 Range("K10").Locked = True
 Else
 Range("K10").Locked = False
 End If
  If Range("AI6") = 3 Then
 Range("K12").Locked = True
 Else
 Range("K12").Locked = False
 End If
If Range("AI7") = 3 Then
 Range("K14").Locked = True
 Else
 Range("K14").Locked = False
 End If
  If Range("AI8") = 3 Then
 Range("K16").Locked = True
 Else
 Range("K16").Locked = False
 End If
If Range("AI9") = 3 Then
 Range("K18").Locked = True
 Else
 Range("K18").Locked = False
 End If
  If Range("AI10") = 3 Then
 Range("K20").Locked = True
 Else
 Range("K20").Locked = False
 End If
If Range("AI11") = 3 Then
 Range("K22").Locked = True
 Else
 Range("K22").Locked = False
 End If
  If Range("AI12") = 3 Then
 Range("K24").Locked = True
 Else
 Range("K24").Locked = False
 End If
If Range("AI13") = 3 Then
 Range("K26").Locked = True
 Else
 Range("K26").Locked = False
 End If
  If Range("AI14") = 3 Then
 Range("K28").Locked = True
 Else
 Range("K28").Locked = False
 End If
If Range("AI15") = 3 Then
 Range("K30").Locked = True
 Else
 Range("K30").Locked = False
 End If
  If Range("AI16") = 3 Then
 Range("M8").Locked = True
 Else
 Range("M8").Locked = False
 End If
If Range("AI17") = 3 Then
 Range("M10").Locked = True
 Else
 Range("M10").Locked = False
 End If
  If Range("AI18") = 3 Then
 Range("M12").Locked = True
 Else
 Range("M12").Locked = False
 End If
If Range("AI19") = 3 Then
 Range("M14").Locked = True
 Else
 Range("M14").Locked = False
 End If
  If Range("AI20") = 3 Then
 Range("O8").Locked = True
 Else
 Range("O8").Locked = False
 End If
If Range("AI21") = 3 Then
 Range("O10").Locked = True
 Else
 Range("O10").Locked = False
 End If
  If Range("AI22") = 3 Then
 Range("O12").Locked = True
 Else
 Range("O12").Locked = False
 End If
If Range("AI23") = 3 Then
 Range("Q8").Locked = True
 Else
 Range("Q8").Locked = False
 End If
  If Range("AI24") = 3 Then
 Range("Q10").Locked = True
 Else
 Range("Q10").Locked = False
 End If
If Range("AI25") = 3 Then
 Range("Q12").Locked = True
 Else
 Range("Q12").Locked = False
 End If
  If Range("AI26") = 3 Then
 Range("S8").Locked = True
 Else
 Range("S8").Locked = False
 End If
If Range("AI27") = 3 Then
 Range("S10").Locked = True
 Else
 Range("S10").Locked = False
 End If
  If Range("AI28") = 3 Then
 Range("S12").Locked = True
 Else
 Range("S12").Locked = False
 End If
If Range("AI29") = 3 Then
 Range("U8").Locked = True
 Else
 Range("U8").Locked = False
 End If
  If Range("AI30") = 3 Then
 Range("U10").Locked = True
 Else
 Range("U10").Locked = False
 End If
If Range("AI31") = 3 Then
 Range("U12").Locked = True
 Else
 Range("U12").Locked = False
 End If
  If Range("AI32") = 3 Then
 Range("U14").Locked = True
 Else
 Range("U14").Locked = False
 End If
If Range("AI33") = 3 Then
 Range("W8").Locked = True
 Else
 Range("W8").Locked = False
 End If
  If Range("AI34") = 3 Then
 Range("W10").Locked = True
 Else
 Range("W10").Locked = False
 End If
If Range("AI35") = 3 Then
 Range("W12").Locked = True
 Else
 Range("W12").Locked = False
 End If
  If Range("AI36") = 3 Then
 Range("W14").Locked = True
 Else
 Range("W14").Locked = False
 End If
If Range("AI37") = 3 Then
 Range("W16").Locked = True
 Else
 Range("W16").Locked = False
 End If
  If Range("AI38") = 3 Then
 Range("W18").Locked = True
 Else
 Range("W18").Locked = False
 End If
If Range("AI39") = 3 Then
 Range("W20").Locked = True
 Else
 Range("W20").Locked = False
 End If
  If Range("AI40") = 3 Then
 Range("W22").Locked = True
 Else
 Range("W22").Locked = False
 End If
If Range("AI41") = 3 Then
 Range("W24").Locked = True
 Else
 Range("W24").Locked = False
 End If
  If Range("AI42") = 3 Then
 Range("W26").Locked = True
 Else
 Range("W26").Locked = False
 End If
If Range("AI43") = 3 Then
 Range("W28").Locked = True
 Else
 Range("W28").Locked = False
 End If
  If Range("AI44") = 3 Then
 Range("W30").Locked = True
 Else
 Range("W30").Locked = False
 End If
If Range("AI45") = 3 Then
 Range("W32").Locked = True
 Else
 Range("W32").Locked = False
 End If
Sheets("Update Room").Protect
End If
End Sub

[jonh]

Hello.

Sub test()

    Dim S, n, a, i, l
    
    S = "I8,I10,I12,K8,K10,K12,K14,K16,K18,K20,K22,K24," & _
        "K26,K28,K30,M8,M10,M12,M14,O8,O10,O12,Q8,Q10,Q12," & _
        "S8,S10,S12,U8,U10,U12,U14,W8,W10,W12,W14,W16,W18," & _
        "W20,W22,W24,W26,W28,W30,W32"
    a = Split(S, ",")
    For i = 0 To UBound(a)
        If Range("AI" & i + 1) = 3 Then
            If Len(l) Then l = l & ","
            l = l & a(i)
        Else
            If Len(n) Then n = n & ","
            n = n & a(i)
        End If
    Next
    Range(n).Locked = False
    Range(l).Locked = True
End Sub

[Juriemagic]

hello Jonh,

I get an error

method'Range'of object'_Worksheet'failed

In the code the part that says Range(1).Locked=True is highlighted in yellow...

[Aflatoon]

It's Range(L) not Range(1) - did you copy and paste the code?

Note: if all, or none, of the cells in AI contain 3, you will get an error, so you should test the strings first:

If len(n) <> 0 then Range(n).Locked = False
If len(l) <> 0 then Range(l).Locked = True

[Juriemagic]

Yes I copied and pasted. It looked like a "1", I checked with the keyboard and noticed it was a small (L)..sorry about that. Your correction did the trick...wow!!!..amazing!!. I appreciate your help more than you will know, thank you very very much...have a blessed day!

[snb]

removed the code