根据细胞中的变化保护和格式化指定的单元格

Question

我有一个Cellrange(U4：U50(，它允许您在"是"one_answers"否"之间进行选择。对于每一行，当相关的单元格中相关单元格时，我都需要在右侧格式化和保护右侧的单元格(v4：al4，v4：al4，等(，直到v50：al50(。

。

我只能根据我的小知识将代码的几部分组合在一起：我设法根据下面的代码对第4行进行了所需的更改。

protect and notect sub在本工作书中，它们就是这样做的。

 Sub Worksheet_Change(ByVal Target As Range)
 Set checkRange = Application.Intersect(Target, Range("U4:U50"))
' If the change wasn't in this range then we're done
    If checkRange Is Nothing Then Exit Sub
 If Range("U4").Value = "Yes" Then
        Range("V4:AL4").Select
        Call ActiveWorkbook.UNprotect_all_sheets
        With Selection
        .Locked = True
        End With
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = -9.99786370433668E-02
        .PatternTintAndShade = 1
        End With
        Range("U4").Select

    ElseIf Range("U4").Value <> "Yes" Then
        Call ActiveWorkbook.UNprotect_all_sheets
        Range("V4:AL4").Select
    With Selection
        .Locked = False
        End With
        With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0

       End With

    End If
       Call ActiveWorkbook.Protect_all_sheets
End Sub

下一步是使代码根据目标范围的所有行工作，所以我从此开始

Dim r As Long
Dim c As Long
' 21 targets column U 
c = 21
For r = 4 To 50
If Cells(r, c).Value = "Yes" Then
'here I think the process would be to unprotect the sheet, then select from (r,c+1) to (r,c+17), apply the formatting (shade and protection), go to next r and at the end protect the sheet again

但是我的问题是我现在知道如何：

1. 选择从细胞(R，C 1(到细胞(R，C 17(;
的细胞范围
2. 将指令相对于正确的排。

对此的任何评论都非常欢迎！

感谢你们所有人，希望您可以从我的阐述中了解我需要做的事情。

我一直在寻找答案，也许我无法寻找正确的措辞。

Answer 1

您可以这样做。通常，无需选择任何东西，但是我已经把它留在了，因为尚不清楚您的其他潜艇是否正在选择选择。您可以使用调整大小，但我不能烦恼从V到Al。

弄清楚它是多少列。

在反射时，可能像第二个块那样重新配置第一个块(也许在选择之前都应在选择之前调用未保护(。

严格来说，代码应迎合多个单元格。为此，您可以将Target的实例更改为Target(1)。

Sub Worksheet_Change(ByVal Target As Range)
Set checkRange = Application.Intersect(Target, Range("U4:U50"))
' If the change wasn't in this range then we're done
If checkRange Is Nothing Then Exit Sub
If Target.Value = "Yes" Then
    Range(Cells(Target.Row, "V"), Cells(Target.Row, "AL")).Select
    Call ActiveWorkbook.UNprotect_all_sheets
    With Selection
        .Locked = True
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark2
            .TintAndShade = -9.99786370433668E-02
            .PatternTintAndShade = 1
        End With
    End With
Else
    Call ActiveWorkbook.UNprotect_all_sheets
    With Range(Cells(Target.Row, "V"), Cells(Target.Row, "AL"))
        .Locked = False
        With .Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End With
End If
Call ActiveWorkbook.Protect_all_sheets
End Sub