Excel宏开发需求：判断活动单元格对应首行颜色并执行校验操作

Got it, let's build this macro to handle your required field validation based on the header cell's color. Here's a step-by-step solution tailored to your needs:

The Complete VBA Code #

This macro uses the Worksheet_SelectionChange event to check if the user is leaving a required empty cell (with a red header) and prompts them to fill it in before moving on.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static previousCell As Range
    Dim headerCell As Range
    
    ' Validate the previously selected cell if it exists
    If Not previousCell Is Nothing Then
        ' Get the header cell from the same column, first row
        Set headerCell = Me.Cells(1, previousCell.Column)
        
        ' Check if header is red (ColorIndex 48) and cell is empty
        If headerCell.Interior.ColorIndex = 48 And IsEmpty(previousCell.Value) Then
            MsgBox "Required field.", vbExclamation, "Missing Input"
            ' Force focus back to the required cell
            previousCell.Select
            Exit Sub
        End If
    End If
    
    ' Update the previous cell reference to the new selection
    Set previousCell = Target
End Sub

How It Works #

Let's break down the key parts to make it clear:

• Worksheet_SelectionChange Event: Triggers every time the user selects a new cell, which is perfect for validating the cell they're trying to leave.
• Static previousCell: Keeps track of the last cell the user had selected, so we can check it after they click away to a new cell.
• Header Cell Lookup: Me.Cells(1, previousCell.Column) grabs the cell in the first row of the same column as the previous cell (your header row).
• Validation Logic: If the header is red (ColorIndex 48) and the previous cell is empty, it shows a warning message and forces the user back to the required cell. Green headers are completely ignored, just like you asked.

How to Add This Macro to Your Excel File #

1. Open your Excel workbook and press Alt + F11 to open the VBA Editor.
2. In the left-hand Project Explorer, find the worksheet you want this logic applied to (e.g., Sheet1) and double-click it.
3. Paste the code into the blank code window that opens.
4. Save your file as an Excel Macro-Enabled Workbook (*.xlsm)—regular .xlsx files don't support macros, so this step is crucial.

Quick Notes #

• Double-check your red header's ColorIndex: Select a red header cell, open the Immediate Window (Ctrl + G), type ?ActiveCell.Interior.ColorIndex, and press Enter to confirm it's 48. If not, update the code with the correct index.
• If your header row isn't the first row (row 1), modify Me.Cells(1, previousCell.Column) to use your actual header row number (e.g., Me.Cells(2, previousCell.Column) for row 2).

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