Hey there! Let's tackle your two VBA requirements step by step. I've rewritten and optimized the code to make it cleaner, more efficient, and aligned with your exact needs:

1. 整行条件格式（按年龄着色） #

We'll directly apply conditional formatting rules to your data range, coloring entire rows based on the age values in column C. This avoids the tedious manual cell selection from your original code and adapts automatically if your data grows.

Sub ApplyRowConditionalFormatting()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range
    
    ' Target your worksheet (update the name if needed)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Define the full data range (columns A to D, from header row to last data row)
    Set dataRange = ws.Range("A2:D" & lastRow)
    
    ' Clear existing conditional formatting to avoid conflicts
    dataRange.FormatConditions.Delete
    
    ' Rule 1: Age <=18 → Gray fill
    With dataRange.FormatConditions.Add(Type:=xlExpression, Formula1:="=$C2<=18")
        With .Interior
            .Color = RGB(217, 217, 217) ' Neutral gray
        End With
    End With
    
    ' Rule 2: Age 19-25 → Yellow fill
    With dataRange.FormatConditions.Add(Type:=xlExpression, Formula1:="=$C2>=19 AND $C2<=25")
        With .Interior
            .Color = RGB(255, 255, 153) ' Soft yellow
        End With
    End With
    
    ' Rule 3: Age 26-40 → Dark yellow fill
    With dataRange.FormatConditions.Add(Type:=xlExpression, Formula1:="=$C2>=26 AND $C2<=40")
        With .Interior
            .Color = RGB(255, 204, 0) ' Deep yellow
        End With
    End With
    
    ' Rule 4: Age 41-60 → Orange fill
    With dataRange.FormatConditions.Add(Type:=xlExpression, Formula1:="=$C2>=41 AND $C2<=60")
        With .Interior
            .Color = RGB(255, 153, 0) ' Warm orange
        End With
    End With
    
    ' Rule 5: Age >=61 → Green fill
    With dataRange.FormatConditions.Add(Type:=xlExpression, Formula1:="=$C2>=61")
        With .Interior
            .Color = RGB(146, 208, 80) ' Soft green
        End With
    End With
End Sub

• The $C2 in formulas locks the column reference, ensuring the rule applies to the entire row based on the age in column C.
• Adjust the RGB values if you need to tweak the exact color shades.

2. Sort by Name Group, then Presentation Date (Ascending) #

We'll use Excel's built-in sort functionality to first group rows by name, then sort each group's presentation dates from oldest to newest.

Sub SortByNameThenDate()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim sortRange As Range
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Include the header row in the sort range
    Set sortRange = ws.Range("A1:D" & lastRow)
    
    ' Clear existing sort settings
    ws.Sort.SortFields.Clear
    
    ' Primary sort key: Name (Column A), ascending order
    ws.Sort.SortFields.Add Key:=ws.Range("A1:A" & lastRow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    ' Secondary sort key: Presentation Date (Column D), ascending order
    ws.Sort.SortFields.Add Key:=ws.Range("D1:D" & lastRow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    ' Execute the sort
    With ws.Sort
        .SetRange sortRange
        .Header = xlYes ' Confirm first row is a header
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin ' Use xlLetter for English name sorting
        .Apply
    End With
End Sub

• Setting .Header = xlYes ensures Excel doesn't treat your column headers as data rows.
• Switch SortMethod to xlLetter if you're sorting English names instead of Chinese.

Combine Both Tasks (Optional) #

Run this single sub to execute both the conditional formatting and sorting in one go:

Sub RunAllTasks()
    ApplyRowConditionalFormatting
    SortByNameThenDate
End Sub

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