如何使用宏动态修改Excel行颜色：基于列重复值与指定列条件

Got it, let's solve this problem step by step. You want to color rows where a column (like Patch number) has duplicates, but only the rows with the lower value in another column (like Patch version)—exactly like your example where row 1 (version 6) gets colored while row 2 (version 7) stays as-is. Here's a practical, customizable macro to make this happen:

Step 1: Create the Macro #

First, open the VBA Editor (press Alt + F11), then insert a new module (go to Insert > Module). Paste this code into the module:

Sub ColorDuplicateRowsByVersion()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim patchDict As Object
    Dim i As Long
    Dim patchNum As Variant
    Dim maxVersion As Double
    
    ' Set your target worksheet (replace "Sheet1" with your actual sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' Find the last row with data in the Patch number column (column B)
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' Use a dictionary to track the highest version number for each patch
    Set patchDict = CreateObject("Scripting.Dictionary")
    
    ' First pass: Populate the dictionary with max version per patch number
    For i = 2 To lastRow ' Start at row 2 (skip header row)
        patchNum = ws.Cells(i, "B").Value
        If Not patchDict.Exists(patchNum) Then
            patchDict(patchNum) = ws.Cells(i, "C").Value
        Else
            ' Update the max version if current row's version is higher
            If ws.Cells(i, "C").Value > patchDict(patchNum) Then
                patchDict(patchNum) = ws.Cells(i, "C").Value
            End If
        End If
    Next i
    
    ' Second pass: Color rows that have a duplicate patch number AND lower version than the max
    For i = 2 To lastRow
        patchNum = ws.Cells(i, "B").Value
        ' Check if the patch has duplicates AND current version is not the highest
        If WorksheetFunction.CountIf(ws.Range("B:B"), patchNum) > 1 And _
           ws.Cells(i, "C").Value < patchDict(patchNum) Then
            ' Set row background color (adjust RGB values to your preferred color)
            ws.Rows(i).Interior.Color = RGB(255, 204, 204) ' Light red
        Else
            ' Reset color if the row doesn't meet the condition
            ws.Rows(i).Interior.ColorIndex = xlColorIndexNone
        End If
    Next i
    
    ' Clean up objects
    Set patchDict = Nothing
    Set ws = Nothing
    MsgBox "Row coloring completed successfully!", vbInformation
End Sub

Step 2: Customize the Macro #

Adjust these parts to match your spreadsheet:

• Worksheet name: Replace "Sheet1" with the name of your sheet (e.g., "PatchData").
• Columns: The code assumes Patch number is in column B and Patch version in column C. If your columns are different, change the letters (e.g., "D" for column D).
• Row color: Modify the RGB(255,204,204) value to your desired color. Use an RGB color picker to find the right values.
• Header row: If your data starts at a different row (not row 2), update the For i = 2 To lastRow lines to match.

Step 3: Run the Macro #

• Go back to your Excel sheet, press Alt + F8, select ColorDuplicateRowsByVersion, and click Run.
• Alternatively, you can add a button to your sheet to run the macro with one click (right-click the ribbon > Customize the Ribbon > add a button and assign the macro to it).

How It Works #

1. First pass: The macro uses a dictionary to store the highest Patch version for each unique Patch number. This lets us quickly compare each row's version to the maximum for its patch.
2. Second pass: For each row, it checks if the Patch number has duplicates (using CountIf) and if the row's version is lower than the maximum. If both conditions are true, it colors the row.
3. Reset color: Rows that no longer meet the condition (e.g., if you update a version to be the highest) will have their background color removed.

Example Behavior #

Using your sample data:

The macro will detect that Patch number 123456 has duplicates, and that version 6 is lower than the max version 7. It will color row 1 with your chosen color, leaving row 2 uncolored.

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