Filter a column based on its colour using VBA

[justme101]

Hello Folks,

I have a code which is supposed to colour all repeating values in a column with a particular colour and then filter only those cells which are coloured. The colouring part works fine, but I am not able to filter the cells which are coloured. Here's the code:

Dim myRange As Range
Dim i As Integer
Dim j As Integer
Dim myCell As Range
Set myRange = Range("A:A")

For Each myCell In myRange
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
myCell.Interior.ColorIndex = 3
End If
Next

Dim cel As Range, rng As Range
Set rng = Sheets("Data").Range("A1", Sheets("Data").Range("A65536").End(xlUp).Offset(, 1))

'** Start AutoFilter Process
With Sheets("Data").Rows("1:65536")
.AutoFilter
'** Use temporary column 1
.AutoFilter Field:=1, Criteria1:="Interior.ColorIndex = 3"
End With

Requesting your experience on this. Thank you.

 

[Fluff]

How about

With Sheets("Data")
   .Range("A1").AutoFilter 1, RGB(255, 0, 0), xlFilterCellColor
End With

 

[mumps]

Try:

Sub FilterByColour()
    Application.ScreenUpdating = False
    Dim myRange As Range
    Set myRange = Range("A:A")
    With Sheets("Data")
        For Each myCell In .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
            If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
                myCell.Interior.ColorIndex = 3
            End If
        Next myCell
        .Range("A1").AutoFilter Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor
    End With
    Application.ScreenUpdating = True
End Sub

 

[justme101]

How about

With Sheets("Data")
   .Range("A1").AutoFilter 1, RGB(255, 0, 0), xlFilterCellColor
End With

Works! Thank you.

 

[justme101]

W

Try:

Sub FilterByColour()
    Application.ScreenUpdating = False
    Dim myRange As Range
    Set myRange = Range("A:A")
    With Sheets("Data")
        For Each myCell In .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
            If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
                myCell.Interior.ColorIndex = 3
            End If
        Next myCell
        .Range("A1").AutoFilter Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor
    End With
    Application.ScreenUpdating = True
End Sub

This works, but I already marked the previous reply as the answer. Thank you so much for your time though.

 

[mumps]

Glad we could help.

 

[Fluff]

Works! Thank you.

My pleasure