Excel VBA按其他列规则高亮行、跨工作表应用实现需求咨询

• 实现A1:A145区域高亮着色：A列着色按照固定行数分组，前一组标黄色、后一组标蓝色交替显示，每组行数支持动态调整；同时B列需要循环重复填充D列的所有数值
• 功能需要支持Sheet1到Sheet8以及更多工作表批量生效，现有代码仅能从C列复制颜色和数值到A列末尾，无法满足基于指定规则高亮对应行的需求

Sub Color_My_Cells()

Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim Lastrowa As Long
Lastrowa = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To Lastrowa
Cells(Lastrow, 1).Resize(Cells(i, 2).Value) = Cells(i, 2).Value
Cells(Lastrow, 1).Resize(Cells(i, 2)).Interior.Color = Cells(i, 2).Interior.Color
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Next
Application.ScreenUpdating = True
End Sub

以下代码可直接满足需求，参数可根据实际场景调整：

Sub BatchProcessSheets()
    ' 可配置参数，按需修改
    Const GROUP_ROWS As Long = 14 ' 每组行数，动态调整直接改这个值即可
    Const TARGET_A_ROWS As Long = 145 ' A列要处理的行数
    Dim color1 As Long, color2 As Long
    color1 = RGB(255, 255, 0) ' 黄色，可自定义色值
    color2 = RGB(0, 0, 255) ' 蓝色，可自定义色值
    
    Dim ws As Worksheet
    Dim i As Long, j As Long, dCount As Long, dArr As Variant
    
    Application.ScreenUpdating = False
    ' 遍历所有工作表，也可以指定只处理Sheet1到Sheet8，替换为For i = 1 To 8: Set ws = Sheets(i): 即可
    For Each ws In ThisWorkbook.Worksheets
        ' 处理A列着色
        For i = 1 To TARGET_A_ROWS Step GROUP_ROWS * 2
            ws.Range("A" & i).Resize(IIf(i + GROUP_ROWS - 1 > TARGET_A_ROWS, TARGET_A_ROWS - i + 1, GROUP_ROWS)).Interior.Color = color1
            If i + GROUP_ROWS <= TARGET_A_ROWS Then
                ws.Range("A" & i + GROUP_ROWS).Resize(IIf(i + GROUP_ROWS * 2 - 1 > TARGET_A_ROWS, TARGET_A_ROWS - (i + GROUP_ROWS) + 1, GROUP_ROWS)).Interior.Color = color2
            End If
        Next i
        
        ' 处理B列重复填充D列数值
        dArr = ws.Range("D1:D" & ws.Cells(ws.Rows.Count, "D").End(xlUp).Row).Value
        dCount = UBound(dArr, 1)
        For i = 1 To TARGET_A_ROWS
            ws.Cells(i, "B").Value = dArr(((i - 1) Mod dCount) + 1, 1)
        Next i
    Next ws
    Application.ScreenUpdating = True
    MsgBox "批量处理完成"
End Sub

代码说明 #

• 如需只处理指定工作表，把遍历所有工作表的For Each ws In ThisWorkbook.Worksheets替换为指定范围即可，比如仅处理Sheet1到Sheet8可以写成For i = 1 To 8: Set ws = Sheets(i):
• 分组行数、处理行数、两种着色的色值都在代码开头的可配置参数段，直接修改对应数值即可，不需要调整逻辑代码
• B列会自动循环D列所有非空值填充，D列内容修改后重新运行代码即可更新B列内容

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