VBA code required: Select all cells in the worksheet that are shaded the same colour as the active cell

[NicoMellett]

I have been Googling for ages, but for some reason I can't find a simple solution to this problem. All I want to do is run a macro, and Excel selects the cells in the current worksheet that are shaded the same colour as the current cell.

P.S. No need to consider Conditional Formatting.

Thanks in advance!

Cheers
Nico

 

[Yongle]

Try this

Sub SelectCells1()      'Includes active cell
    Dim Kolor As Long, Cel As Range, Rng As Range
    Kolor = activeCell.DisplayFormat.Interior.Color
    Set Rng = activeCell
    For Each Cel In ActiveSheet.UsedRange
        If Cel.DisplayFormat.Interior.Color = Kolor Then Set Rng = Union(Rng, Cel)
    Next Cel
    If Not Rng Is Nothing Then Rng.Select
End Sub

Sub SelectCells2()      'Excludes active cell
    Dim Kolor As Long, Cel As Range, Rng As Range
    Kolor = activeCell.DisplayFormat.Interior.Color
    For Each Cel In ActiveSheet.UsedRange
        If Not Cel.Address = activeCell.Address Then
            If Cel.DisplayFormat.Interior.Color = Kolor Then
                If Not Rng Is Nothing Then Set Rng = Union(Rng, Cel) Else Set Rng = Cel
            End If
        End If
    Next Cel
    If Not Rng Is Nothing Then Rng.Select
End Sub

 

[NicoMellett]

Perfect! Thanks for the help

 

[Yongle]

thanks for your feedback