VBA动态行高亮需求：D列与K列匹配且K列非空时高亮整行

Hey there! Since you're new to VBA, let's break down your two key requirements with simple, actionable solutions—no overly complex jargon, promise.

1. Sorting the Dynamic Dataset Starting at Row 3 #

First, handling the daily-changing row count is easy with VBA's End(xlUp) method to find the last used row. Here's a snippet that sorts your data (adjust the sort column as needed—this example uses column A):

Sub SortDynamicData()
    Dim lastRow As Long
    ' Find the last row with data in column A (change to your key column if needed)
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    ' Define the data range from row 3 to lastRow, covering columns A to K (adjust columns if needed)
    Dim dataRange As Range
    Set dataRange = Range("A3:K" & lastRow)
    
    ' Sort the range—here we sort by column A in ascending order
    dataRange.Sort _
        Key1:=Range("A3"), _
        Order1:=xlAscending, _
        Header:=xlNo ' Use xlYes if your row 3 has headers
End Sub

Note: If your dataset has headers in row 3, change Header:=xlNo to Header:=xlYes.

2. Highlighting Rows Where D Column = K Column (and K is Not Empty) #

You mentioned considering End(xlUp) for formulas or conditional formatting—let's cover both approaches:

Option A: VBA Approach (for automated highlighting) #

This loop checks each row from 3 to the last row and applies highlighting when your condition is met:

Sub HighlightMatchingRows()
    Dim lastRow As Long
    Dim i As Long
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row ' Use D column to find last row
    
    For i = 3 To lastRow
        ' Check if D equals K and K isn't blank
        If Cells(i, "D").Value = Cells(i, "K").Value And Cells(i, "K").Value <> "" Then
            ' Highlight the entire row (adjust columns A:K to match your data width)
            Cells(i, "A").Resize(1, 11).Interior.Color = RGB(255, 255, 153) ' Light yellow
        Else
            ' Optional: Clear highlighting if condition isn't met
            Cells(i, "A").Resize(1, 11).Interior.ColorIndex = xlColorIndexNone
        End If
    Next i
End Sub

Option B: Conditional Formatting (no VBA needed) #

If you prefer a non-VBA solution, conditional formatting works perfectly here:

• Select your entire data range starting at row 3 (you can use Ctrl+Shift+Down after clicking cell A3 to select dynamically)
• Go to the Home tab > Conditional Formatting > New Rule
• Choose "Use a formula to determine which cells to format"
• Enter this formula: =AND($D1=$K1, $K1<>"")
  (The $ locks the column references, so each row checks its own D and K values)
• Click Format > Go to the Fill tab > Pick your highlight color > Click OK twice

You can even combine both sorting and highlighting into one VBA sub if you want to run them together!

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