• 找到最后一个彩色行并在下一行设置highlight

Option Explicit

Sub HightlightRow()
    Dim c As Range, Sht As Worksheet, iRow As Long
    With Application.FindFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = vbGreen ' modify as needed
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    For Each Sht In Worksheets
        Set c = Sht.Cells.Find(What:="", After:=Sht.Range("A1"), _
            LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, MatchCase:=False, MatchByte:=False, _
            SearchFormat:=True)
        If c Is Nothing Then
            iRow = 2  ' the first data row, skip header
        Else
            iRow = c.Row + 1 ' next row
        End If
        Set c = Application.Intersect(Sht.Rows(iRow), Sht.UsedRange)
        If Not c Is Nothing Then
            c.Interior.Color = vbGreen
        End If
    Next
End Sub

字符串

的数据

例如，您在第一行中有一个状态=颜色的图例：单元格A1 -付款完成，B1-付款未完成等。然后您通过宏选择范围并检查范围内的所有单元格是否以您需要的颜色突出显示，如下所示：

Sub Check_Payments()
Dim StatusColour as Variant, count1 as Long, sRange as Range, count as Long
StatusColour=Cells(1,1).Interior.Color'pick the status color from the legend 
Set sRange=ActiveSheet.Range(<range need to check, for example"A2:A10">) 
count1=0
count=0
For Each x in sRange
count=count+1
If x.Interior.Color=StatusColour Then count1=count1+1'check if the highlight of the cell is the status color
Next x
If count1=count1 Then MsgBox "All paid" Else MsgBox "Some assholes didn't pay"
End Sub

字符串