Simplificar código

Question

Boa noite

Será que tem uma maneira de simplificar o código abaixo, só na planilha 3 ele ficará gigante, tem mais 5 planilha para inserir os códigos.

Sub data()
Plan3.Unprotect Password:="far"
Plan3.Range("F13:IS513").Locked = True
If Date = Range("F9") Then
For L = 13 To 513
If Cells(L, 6) = "" And Cells(L, 7) = "" And Cells(L, 8) = "" And Cells(L, 9) = "" Then
Plan3.Range("F" & L).Locked = False
Else
Plan3.Range("F" & L).Locked = True
End If
Next L
'
For L = 13 To 513
If Cells(L, 7) = "" And Cells(L, 8) = "" And Cells(L, 9) = "" Then
'If Cells(L, 11) = "" Then
Plan3.Range("G" & L).Locked = False
Else
Plan3.Range("G" & L).Locked = True
End If
Next L
'
For L = 13 To 513
If Cells(L, 8) = "" And Cells(L, 9) = "" Then
'If Cells(L, 12) = "" Then
Plan3.Range("H" & L).Locked = False
Else
Plan3.Range("H" & L).Locked = True
End If
Next L
'
For L = 13 To 513
If Cells(L, 9) = "" Then
Plan3.Range("I" & L).Locked = False
Else
Plan3.Range("I" & L).Locked = True
End If
Next L
End If
Plan3.Protect Password:="far"
End Sub

Sub data2()
Plan3.Unprotect Password:="far"
Plan3.Range("F13:IS513").Locked = True
If Date = Range("J9") Then
For L = 13 To 513
If Cells(L, 10) = "" And Cells(L, 11) = "" And Cells(L, 12) = "" And Cells(L, 13) = "" Then
Plan3.Range("J" & L).Locked = False
Else
Plan3.Range("J" & L).Locked = True
End If
Next L
'
For L = 13 To 513
If Cells(L, 11) = "" And Cells(L, 12) = "" And Cells(L, 13) = "" Then
'If Cells(L, 11) = "" Then
Plan3.Range("K" & L).Locked = False
Else
Plan3.Range("K" & L).Locked = True
End If
Next L
'
For L = 13 To 513
If Cells(L, 12) = "" And Cells(L, 13) = "" Then
'If Cells(L, 12) = "" Then
Plan3.Range("L" & L).Locked = False
Else
Plan3.Range("L" & L).Locked = True
End If
Next L
'
For L = 13 To 513
If Cells(L, 13) = "" Then
Plan3.Range("M" & L).Locked = False
Else
Plan3.Range("M" & L).Locked = True
End If
Next L
'
End If
Plan3.Protect Password:="far"
End Sub

Ai só 2 comparações que vai até 60 na data

comparações essa que vai de (F9; J9; N9; R9; V9; Z9; AD9; AH9; até IP9)

Obrigado pela atenção.

Answer 1

Simplifiquei o subprocedimento data para:

Sub data()
    Dim lngLin As Long
    
    Plan3.Unprotect Password:="far"
    Plan3.Range("F13:IS513").Locked = True
    If Date = Range("F9") Then
        For lngLin = 13 To 513
            If Cells(lngLin, 6) = "" And Cells(lngLin, 7) = "" And Cells(lngLin, 8) = "" And Cells(lngLin, 9) = "" Then
                Plan3.Range("F" & lngLin).Locked = False
            End If
            If Cells(lngLin, 7) = "" And Cells(lngLin, 8) = "" And Cells(lngLin, 9) = "" Then
                Plan3.Range("G" & lngLin).Locked = False
            End If
            If Cells(lngLin, 8) = "" And Cells(lngLin, 9) = "" Then
                Plan3.Range("H" & lngLin).Locked = False
            End If
            If Cells(lngLin, 9) = "" Then
                Plan3.Range("I" & lngLin).Locked = False
            End If
        Next lngLin
    End If
    
    Plan3.Protect Password:="far"
End Sub

Qual seria a continuação desse código? Não entendi a lógica.

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Answer 2

Acho que entendi. Experimente:

Sub data()
    Dim lngLin As Long
    Dim lngCol As Long
    
    Plan3.Unprotect Password:="far"
    Plan3.Range("F13:IS513").Locked = True
    
    For lngCol = 6 To 250 Step 4
    If Cells(9, lngCol) = Date Then
        For lngLin = 13 To 513
            If Cells(lngLin, lngCol) = "" _
            And Cells(lngLin, lngCol + 1) = "" _
            And Cells(lngLin, lngCol + 2) = "" _
            And Cells(lngLin, lngCol + 3) = "" Then
                Plan3.Cells(lngLin, lngCol).Locked = False
            End If
            If Cells(lngLin, lngCol + 1) = "" _
            And Cells(lngLin, lngCol + 2) = "" _
            And Cells(lngLin, lngCol + 3) = "" Then
                Plan3.Cells(lngLin, lngCol + 1).Locked = False
            End If
            If Cells(lngLin, lngCol + 2) = "" _
            And Cells(lngLin, lngCol + 3) = "" Then
                Plan3.Cells(lngLin, lngCol + 2).Locked = False
            End If
            If Cells(lngLin, lngCol + 3) = "" Then
                Plan3.Cells(lngLin, lngCol + 3).Locked = False
            End If
        Next lngLin
    End If
    
    Plan3.Protect Password:="far"
End Sub

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Answer 3

boa noite

ficou faltando o Next lngCol

coloquei entre End if e Plan3.Protect Password:="far" esta funcionando normal.

obrigado