多列（跳过M列）批量设置条件格式高亮唯一最小值VBA需求

First, let's fix the syntax error in your original code: the COUNTIF formula had an extra closing parenthesis, which would cause a runtime error. The correct formula should be COUNTIF($D$2:$D$13,$D2)=1.

Now, here's a modified solution that automates the process for multiple columns (D to V, excluding M) and works with tables of varying row counts (50-100 rows):

Reusable Subroutine for a Single Table #

This sub takes a range representing your table's data area and applies the conditional formatting to all target columns:

Sub FormatUniqueMinimaInTable(ByVal tableDataRange As Range)
    Dim targetCols As Variant
    Dim colNum As Variant
    Dim colRange As Range
    Dim lastRow As Long
    Dim minExpr As String
    Dim countExpr As String
    
    ' Target columns: D(4) to V(23), skip M(13)
    targetCols = Array(4,5,6,7,8,9,10,11,12,14,15,16,17,18,19,20,21,22,23)
    
    lastRow = tableDataRange.Row + tableDataRange.Rows.Count - 1
    
    For Each colNum In targetCols
        ' Define the full column range in the table
        Set colRange = tableDataRange.Worksheet.Cells(tableDataRange.Row, colNum) _
                       .Resize(tableDataRange.Rows.Count, 1)
        
        ' Clear existing conditional formatting to avoid duplicates
        colRange.FormatConditions.Delete
        
        ' Build dynamic formulas based on the current column
        minExpr = colRange.Cells(1).Address(False, True) & "=MIN(" & colRange.Address(True, True) & ")"
        countExpr = "COUNTIF(" & colRange.Address(True, True) & "," & colRange.Cells(1).Address(False, True) & ")=1"
        
        ' Add the conditional formatting rule
        colRange.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(" & minExpr & "," & countExpr & ")"
        
        ' Apply formatting settings
        With colRange.FormatConditions(colRange.FormatConditions.Count)
            .SetFirstPriority
            With .Font
                .Bold = True
                .Italic = False
                .TintAndShade = 0
            End With
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = vbWhite
                .TintAndShade = 0
            End With
            .StopIfTrue = False
        End With
    Next colNum
End Sub

Process All 6 Tables #

Call the subroutine for each of your tables by specifying their data ranges:

Sub ProcessAllTables()
    ' Update these ranges to match your actual tables
    FormatUniqueMinimaInTable ThisWorkbook.Sheets("Table1").Range("D2:V50")
    FormatUniqueMinimaInTable ThisWorkbook.Sheets("Table2").Range("D2:V75")
    FormatUniqueMinimaInTable ThisWorkbook.Sheets("Table3").Range("D2:V60")
    FormatUniqueMinimaInTable ThisWorkbook.Sheets("Table4").Range("D2:V90")
    FormatUniqueMinimaInTable ThisWorkbook.Sheets("Table5").Range("D2:V80")
    FormatUniqueMinimaInTable ThisWorkbook.Sheets("Table6").Range("D2:V100")
End Sub

Key Details: #

• Dynamic Rows: Automatically adapts to the number of rows in each table (no hardcoded row numbers)
• Column Exclusion: Skips column M by targeting specific column numbers
• Error Fix: Corrects the COUNTIF syntax error from your original code
• Clean Slate: Clears existing conditional formatting on each column before adding the new rule to avoid conflicts
• Reusability: You can easily add or remove tables by updating the ProcessAllTables sub

How to Implement: #

1. Open your Excel file and press Alt + F11 to open the VBA Editor
2. Insert a new module (Right-click your workbook in the Project Explorer > Insert > Module)
3. Paste both subroutines into the module
4. Adjust the ranges in ProcessAllTables to match your actual tables' data areas
5. Run ProcessAllTables (Press F5 in the editor, or assign it to a button in Excel for easier access)

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