单元格变化自动应用条件及C2:C5区域自动填充失败求助（附VBA代码）

Hey there! Let's tackle your two Excel automation requirements and fix that broken Worksheet_Change code you're dealing with. I'll break this down step by step so it's easy to follow.

Requirement 1: Auto-Apply Conditional Formatting When Cells Change #

To automatically apply conditional formatting when specific cells are modified, we can extend the Worksheet_Change event to refresh format rules whenever your target range is edited. Here's a straightforward implementation:

First, define the range you want to monitor (e.g., A2:B10 or your specific cells), then use a helper sub to apply your desired formatting rules.

Helper Sub for Conditional Formatting #

Sub ApplyConditionalFormatting(targetRange As Range)
    ' Clear existing rules to avoid duplicates
    targetRange.FormatConditions.Delete
    
    ' Add your custom rule (adjust this to match your needs)
    With targetRange.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=100")
        .Interior.Color = RGB(255, 204, 204) ' Light red fill for values over 100
        .Font.Bold = True
    End With
End Sub

Requirement 2: Fix Auto-Fill for C2:C5 Changes #

Your existing Worksheet_Change code has a few critical issues that are preventing it from working as expected:

• The code is truncated (the Ra... part is missing, so the fill logic is incomplete)
• If an error occurs after disabling events, Application.EnableEvents might stay off, breaking all future change events
• Using True for approximate matching in Hlookup could return unexpected results
• You’re clearing U2 but never actually populating it with the lookup result

Fixed Complete Code #

Here’s a revised version that addresses all these issues and combines both requirements into one event handler:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim keyCells As Range
    Dim lookupResult As Variant
    Dim formatMonitorRange As Range ' For Requirement 1
    
    ' Define your target ranges (adjust these to match your workbook)
    Set keyCells = Me.Range("C2:C5") ' Triggers auto-fill
    Set formatMonitorRange = Me.Range("A2:B10") ' Triggers conditional formatting
    
    ' Disable events temporarily to prevent infinite loops
    Application.EnableEvents = False
    
    ' Ensure events are re-enabled even if an error occurs
    On Error GoTo Cleanup

    ' --- Handle Requirement 1: Auto-apply conditional formatting ---
    If Not Application.Intersect(formatMonitorRange, Target) Is Nothing Then
        ApplyConditionalFormatting formatMonitorRange
    End If

    ' --- Handle Requirement 2: Auto-fill when C2:C5 changes ---
    If Not Application.Intersect(keyCells, Target) Is Nothing Then
        ' Clear U2 first (as your original code intended)
        Me.Range("U2").ClearContents
        
        ' Perform exact match lookup (switch to True if you need approximate)
        lookupResult = Application.WorksheetFunction.HLookup("2017-S1", Me.Range("D11:AF11"), 1, False)
        
        ' Only fill U2 if the lookup found a valid result (avoid #N/A errors)
        If Not IsError(lookupResult) Then
            Me.Range("U2").Value = lookupResult
            ' If you need to fill a range instead of a single cell, adjust here:
            ' Me.Range("U2:U5").Value = lookupResult
        End If
    End If

Cleanup:
    ' Re-enable events - THIS IS CRITICAL!
    Application.EnableEvents = True
    ' Show error message if something went wrong
    If Err.Number <> 0 Then
        MsgBox "Oops, an error occurred: " & Err.Description, vbExclamation
        Err.Clear
    End If
End Sub

' Helper sub for conditional formatting (from Requirement 1)
Sub ApplyConditionalFormatting(targetRange As Range)
    targetRange.FormatConditions.Delete
    With targetRange.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=100")
        .Interior.Color = RGB(255, 204, 204)
        .Font.Bold = True
    End With
End Sub

Key Fixes & Improvements #

• Error Safety: The Cleanup section ensures events are always re-enabled, even if the code hits an error
• Exact Lookup: Switched to False for exact matching in Hlookup (change back to True if you intentionally need approximate matches)
• Complete Logic: Added code to actually populate U2 with the lookup result, plus error checking to avoid #N/A values
• Combined Functionality: Merged both requirements into one event handler for cleaner, more efficient code

Quick Adjustments for Your Use Case #

• Update formatMonitorRange to the cells that should trigger conditional formatting
• Modify the conditional formatting rule in ApplyConditionalFormatting to match your specific needs (e.g., different color, formula-based rules)
• If your original Ra... code was meant to fill a different range instead of U2, update the Me.Range("U2") lines to your target cells

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