Filter data based on cell colour

[Busymanjohn]

Hi guys, I have a sheet of data that has some conditional format in column N which highlights cells in that column which do not match the same cell in column M ( colours them yellow ), I would then like to filter the data to show only those yellow cells .... i did see some VBA posted in here for this but that doesn't work with conditional formatting ,,, is there a solution using conditional formatting or some other way to do this ,, btw ,,, using Excel 2003 ( I know 2007 has a tool that does this ). Thanks

 

[obiron]

You have two choices:
1) introduce another column that holds a value depending on whether the values match and filter on that column
2) a VBA macro that runs down each row and looks at the .color property of the cell in question and then row.visible = false for the particular row.

The second method will be significantly slower if you have any volume of data (e.g. I have just 'fixed' a process that used the 2nd method to use the first and the time to complete was reduced from 90 minutes to 8 minutes - it is doing other things as well...)

 

[Busymanjohn]

Hey Obiron ,,, would love to see your solution for method 2 ,,,, I had already added a separate column to show the value difference between the two columns of data in question and used a filter to show anything not equal to 0 ,, and that works fine ( even showed my colleague the GoTo feature with Visibile cell only so she can copy and paste the information she needs from the initial file into a separate file ) ... but I would love to give her a solution that she can do with the click of a button ,,,, she is actually doing some manual copy paste at the moment ,,,, yeah, you got it , she has no training on Excel at all.