Filter or sort a macro result

[Guest]

The macro highlights cells and the user wants to sort or filter on the
highlighted rows so they can be deleted. Here's the macro.

Sub finddiff()
Application.ScreenUpdating = False

Range("A2", Range("b6900").End(xlUp)).ClearFormats

For Each x In Range("a2", Range("a6900").End(xlUp))
For Each y In Range("b2", Range("b6900").End(xlUp))
If x.Value = y.Value Then z = 1
Next
If z <> 1 Then x.Interior.ColorIndex = 6
z = 0
Next

For Each s In Range("b2", Range("b6900").End(xlUp))

For Each t In Range("a2", Range("a6900").End(xlUp))
If s.Value = t.Value Then v = 1
Next
If v <> 1 Then s.Interior.ColorIndex = 6
v = 0
Next

End Sub

 

[Guest]

hi,
excel does not have a function to sort or filter by color.
this is usually done with a helper column where a value is assigned to the
helper column next to the color and the data is sorted or filtered by the
helper column.
it can be done with your macro but you would have to modify it to add the
helper column and change all of your cell references then add a line of code
If z <> 1 Then
x.Interior.ColorIndex = 6
x.offset(0, -1).value = 1
end if 'you need an end if for every if
see this site for more details.
http://www.cpearson.com/excel/sortbycolor.htm
his way is a little more complicated but it probably works better.

regards
FSt1

 

[Guest]

Thank you! I'll pass it on to the user.

hi,
excel does not have a function to sort or filter by color.
this is usually done with a helper column where a value is assigned to the
helper column next to the color and the data is sorted or filtered by the
helper column.
it can be done with your macro but you would have to modify it to add the
helper column and change all of your cell references then add a line of code
If z <> 1 Then
x.Interior.ColorIndex = 6
x.offset(0, -1).value = 1
end if 'you need an end if for every if
see this site for more details.
http://www.cpearson.com/excel/sortbycolor.htm
his way is a little more complicated but it probably works better.

regards
FSt1