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.
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.