Select all colored cells from conditional formatting

Question

I have a range of cells (K6:M200) in my sheet "Summary" that I need to use a macro to select all cells that are colored based off conditional formatting. There are the following conditions:

1. Colored cells will be continuous from K6 until whatever row does not meet the condition.
2. Not all cells will be the same color.

I'm new to VBA and macros so I'm hoping someone can help me figure out how to do this. I've tried a few formulas already and it has not worked.

Answer 1

I'd suggest something like this:

Sub selectCFColours()
    Dim cell As Range
    Dim selRange As Range

    For Each cell In Range("K6:M200")
        If cell.DisplayFormat.Interior.Color <> cell.Interior.Color Then
            If selRange Is Nothing Then
                Set selRange = cell
            Else
                Set selRange = Union(selRange, cell)
            End If
        End If
    Next

    If Not selRange Is Nothing Then selRange.Select
End Sub

Answer 2

The following code is for a generic FindAll code. This can also be used by setting the Application.FindFormat so it can be used with conditional formatting.

Sub FindBlack()
    Dim FoundRange As Range

    With Application.FindFormat
        .Clear
        .Interior.Color = RGB(0, 0, 0)
    End With
    Set FoundRange = FindAll("", LookIn:=xlFormulas, SearchWhat:=Range("K6:M200"), SearchFormat:=True)

    If Not FoundRange Is Nothing Then Debug.Print FoundRange.Address
End Sub

Function FindAll(What, _
    Optional SearchWhat As Variant, _
    Optional LookIn, _
    Optional LookAt, _
    Optional SearchOrder, _
    Optional SearchDirection As XlSearchDirection = xlNext, _
    Optional MatchCase As Boolean = False, _
    Optional MatchByte, _
    Optional SearchFormat) As Range

    'LookIn can be xlValues or xlFormulas, _
     LookAt can be xlWhole or xlPart, _
     SearchOrder can be xlByRows or xlByColumns, _
     SearchDirection can be xlNext, xlPrevious, _
     MatchCase, MatchByte, and SearchFormat can be True or False. _
     Before using SearchFormat = True, specify the appropriate settings for the Application.FindFormat _
     object; e.g. Application.FindFormat.NumberFormat = "General;-General;""-"""

    Dim SrcRange As Range
    If IsMissing(SearchWhat) Then
        Set SrcRange = ActiveSheet.UsedRange
    ElseIf TypeOf SearchWhat Is Range Then
        Set SrcRange = IIf(SearchWhat.Cells.Count = 1, SearchWhat.Parent.UsedRange, SearchWhat)
    ElseIf TypeOf SearchWhat Is Worksheet Then
        Set SrcRange = SearchWhat.UsedRange
    Else: SrcRange = ActiveSheet.UsedRange
    End If
    If SrcRange Is Nothing Then Exit Function

    'get the first matching cell in the range first
    With SrcRange.Areas(SrcRange.Areas.Count)
        Dim FirstCell As Range: Set FirstCell = .Cells(.Cells.Count)
    End With

    Dim CurrRange As Range: Set CurrRange = SrcRange.Find(What:=What, After:=FirstCell, LookIn:=LookIn, LookAt:=LookAt, _
        SearchDirection:=SearchDirection, MatchCase:=MatchCase, MatchByte:=MatchByte, SearchFormat:=SearchFormat)

    If Not CurrRange Is Nothing Then
        Set FindAll = CurrRange
        Do
            Set CurrRange = SrcRange.Find(What:=What, After:=CurrRange, LookIn:=LookIn, LookAt:=LookAt, _
            SearchDirection:=SearchDirection, MatchCase:=MatchCase, MatchByte:=MatchByte, SearchFormat:=SearchFormat)
            If CurrRange Is Nothing Then Exit Do
            If Application.Intersect(FindAll, CurrRange) Is Nothing Then
                Set FindAll = Application.Union(FindAll, CurrRange)
            Else: Exit Do
            End If
        Loop
    End If
End Function

Answer 3

I realized that, since I know the first value, and part of my macro previously selects the whole range that will be colored, I could build a range to work from using those two values.

So I did:

Dim r1 As Range, r2 As Range
Set r1 = Selection
Set r2 = ActiveSheet.Range("K6")
Range(r2, r1).Select

And it worked. I was just approaching this wrong.