Find all continuous ranges with same color in excel-CodePudding

On an excel sheet, there are some continuous ranges with the same color in a row. The problem is to find all these continuous ranges not as individual cells but as a set of continuous ranges. Tried cell.displayformat.interior.color but getting continuous ranges is the problem. For the below image, we need to get the 3 continuous ranges with the same color.

CodePudding user response：

You could use the Union function to do the heavy lifting - it automatically creates the areas as you add cells to it. So you could iterate a Find, looking for the cell colour, and add it to a found collection of cells with Union.

In your example, this would give you three areas. Skeleton code would be:

Dim cell As Range, foundRange As Range, colouredArea As Range
Dim firstAddr As String

With Application.FindFormat
    .Clear
    .Interior.Color = 10921638
End With

Set cell = Sheet1.Range("A1")
Do While True
    Set cell = Sheet1.Cells.Find( _
        What:="", _
        After:=cell, _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=True)
    If cell Is Nothing Then Exit Do
    If cell.Address = firstAddr Then Exit Do
    
    If firstAddr = "" Then firstAddr = cell.Address
    
    If foundRange Is Nothing Then
        Set foundRange = cell
    Else
        Set foundRange = Union(foundRange, cell)
    End If
Loop

If Not foundRange Is Nothing Then
    For Each colouredArea In foundRange.Areas
        Debug.Print colouredArea.Address
    Next
End If