基于单元格值及行上下文的Excel BOM条件格式VBA需求

Got it, let’s build a robust VBA script that handles exactly your BOM requirements. This code will automatically process each unique part number, apply the correct formatting, and manage row visibility as needed.

Full VBA Code #

Sub FormatBOM()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim partDict As Object
    Dim i As Long
    Dim partNum As String
    Dim hasActive As Boolean
    Dim activeRowIndex As Long
    
    ' Set the worksheet (change "BOM" to your sheet name if needed)
    Set ws = ThisWorkbook.Sheets("BOM")
    ' Initialize dictionary to track unique part numbers
    Set partDict = CreateObject("Scripting.Dictionary")
    
    ' Unhide all rows and clear existing formatting first
    ws.Rows.Hidden = False
    ws.Cells.Interior.ColorIndex = xlNone
    
    ' Get last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' First pass: collect all unique part numbers from column A
    For i = 2 To lastRow ' Assume row 1 is header
        partNum = Trim(ws.Cells(i, "A").Value)
        If partNum <> "" And Not partDict.Exists(partNum) Then
            partDict.Add partNum, 0
        End If
    Next i
    
    ' Second pass: process each unique part number
    For Each partNum In partDict.Keys
        hasActive = False
        activeRowIndex = 0
        
        ' Check if this part has an "Active" entry in column F
        For i = 2 To lastRow
            If Trim(ws.Cells(i, "A").Value) = partNum Then
                If UCase(Trim(ws.Cells(i, "F").Value)) = "ACTIVE" Then
                    hasActive = True
                    activeRowIndex = i
                    Exit For ' No need to check further once found
                End If
            End If
        Next i
        
        ' Apply formatting and visibility based on Active status
        For i = 2 To lastRow
            If Trim(ws.Cells(i, "A").Value) = partNum Then
                If hasActive Then
                    ' Mark Active row green, hide others
                    If i = activeRowIndex Then
                        ws.Cells(i, "A").EntireRow.Interior.Color = vbGreen
                        ws.Rows(i).Hidden = False
                    Else
                        ws.Rows(i).Hidden = True
                        ws.Cells(i, "A").EntireRow.Interior.ColorIndex = xlNone
                    End If
                Else
                    ' No Active entry: mark all rows yellow and keep visible
                    ws.Cells(i, "A").EntireRow.Interior.Color = vbYellow
                    ws.Rows(i).Hidden = False
                End If
            End If
        Next i
    Next partNum
    
    MsgBox "BOM formatting completed successfully!", vbInformation
End Sub

Key Features & Customization Tips #

• Reset First: The script starts by unhiding all rows and clearing existing cell colors to ensure a clean slate.
• Case Insensitive: Checks for "Active" work regardless of capitalization (e.g., "active", "ACTIVE" are both recognized).
• Header Assumption: The code assumes your BOM header is in row 1. If your data starts at a different row, adjust the loop starting index (change 2 to your first data row).
• Color Customization: If you want specific shades instead of default green/yellow, replace vbGreen and vbYellow with RGB values. For example:
  • Light green: RGB(146, 208, 80)
  • Soft yellow: RGB(255, 235, 156)
• Sheet Name: Update ThisWorkbook.Sheets("BOM") to match your actual sheet name if it’s not called "BOM".

How to Use #

1. Open your BOM Excel file.
2. Press Alt + F11 to open the VBA Editor.
3. Right-click your workbook in the Project Explorer > Insert > Module.
4. Paste the code above into the new module.
5. Press F5 to run the script, or assign it to a button on your worksheet for easy access.

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