基于单元格值变化实现指定单元格保护与格式化的VBA技术问询

Got it, let's adjust your code so it handles every row from 4 to 50 instead of just row 4. Here's a complete working solution, plus breakdowns of how it works:

Full Updated Code #

Paste this into the worksheet module (right-click your worksheet tab > View Code) replacing your existing Worksheet_Change sub:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim targetRow As Long
    Dim uCell As Range
    Dim formatRange As Range
    
    ' Turn off events to stop infinite loops when we modify cells
    Application.EnableEvents = False
    
    ' Make sure events get turned back on even if something goes wrong
    On Error GoTo Cleanup
    
    ' Only act if the changed cell is in column A, rows 4-50
    If Not Intersect(Target, Me.Range("A4:A50")) Is Nothing Then
        targetRow = Target.Row
        
        ' Grab the corresponding U cell and the V:AL range for this row
        Set uCell = Me.Range("U" & targetRow)
        Set formatRange = Me.Range("V" & targetRow & ":AL" & targetRow)
        
        ' Use your existing unprotect sub from ThisWorkbook
        ThisWorkbook.UnprotectSheet ' Swap this with your actual sub name if needed
        
        ' Apply formatting based on U's yes/no value
        If uCell.Value = "yes" Then
            ' Example: Unlock cells, remove fill, reset font color
            formatRange.Locked = False
            formatRange.Interior.ColorIndex = xlNone
            formatRange.Font.ColorIndex = xlAutomatic
        ElseIf uCell.Value = "no" Then
            ' Example: Lock cells, gray fill, dark gray font
            formatRange.Locked = True
            formatRange.Interior.ColorIndex = 15 ' Light gray
            formatRange.Font.ColorIndex = 16 ' Dark gray
        End If
        
        ' Use your existing protect sub from ThisWorkbook
        ThisWorkbook.ProtectSheet ' Swap this with your actual sub name if needed
    End If

Cleanup:
    ' Turn events back on
    Application.EnableEvents = True
    ' Show an error message if something broke
    If Err.Number <> 0 Then
        MsgBox "Oops, an error occurred: " & Err.Description, vbExclamation
    End If
End Sub

Key Changes & Explanations #

• Dynamic Row Handling: Instead of hardcoding row 4, we use Target.Row to get the exact row where the change happened in column A. This lets us target the right U cell and V:AL range automatically.
• Event Safety: Application.EnableEvents = False prevents the Worksheet_Change event from firing again when we modify cells in V:AL (which would cause an infinite loop). The Cleanup section ensures events always get turned back on, even if an error occurs.
• Range Validation: Intersect(Target, Me.Range("A4:A50")) checks that the change was actually in the area we care about (column A, rows 4-50) so we don't waste resources on irrelevant changes.

Quick Checks to Ensure It Works #

1. Sub Accessibility: Make sure your protect/unprotect subs in ThisWorkbook are declared as Public so the worksheet module can call them. For example:

   Public Sub UnprotectSheet()
       ' Your existing unprotect code here
   End Sub
   
   Public Sub ProtectSheet()
       ' Your existing protect code here
   End Sub
   

2. Formatting Tweaks: Adjust the color indexes and locked status in the yes/no blocks to match your exact formatting needs.
3. Test It: Change a value in column A (rows 4-50) and verify that the corresponding V:AL range updates based on the "yes"/"no" in column U.

内容的提问来源于stack exchange，提问作者Ema