Autofilter 2 Criteria Issue

[nniedzielski]

I am auto-filtering on a sheet right now that will filter out anything not highlighted:

ActiveSheet.Range("A:D").AutoFilter Field:=2, Operator:= _
                    xlFilterNoFill

I want to now tack onto this code to make it auto-filter for anything not highlighted or anything highlighted red. How can I add on to this?

thanks,

 

[Jonmo1]

Try using the Macro Recorder while you do the filtering by hand.
That should give you the syntax needed.

 

[nniedzielski]

How would I do this by hand? Excel doesnt seem to want to allow me to filter for more than one color in the drop down.

 

[Jonmo1]

You're right, looks like you can only filter by a single color. If the autofilter can't do it, then macro can't either.
How are the cells colored? By hand, or by conditional formatting ?

If by conditional format, then you can filter by that same criteria.

 

[nniedzielski]

they are colored by Macro code, which means i need to rethink about the way they are being colored int he first place,

thanks,

 

[Jonmo1]

which means i need to rethink about the way they are being colored int he first place,

Cell Color is NOT Data that can be querried and parsed.
It's really only meant for your eyes, to make your sheet look pretty.

I would consider alternate methods to 'flag' cells for certain criteria.
Like putting text values in an alternate column..
If A2 is to be colored Red by your macro, then your macro could also put a certain text value in B2 for example.
Then you can filter column B for that word.


Or again, make the autofilter use the same criteria your macro uses to choose which color to put in the cell.

 

[nniedzielski]

i dont disagree at all, thank you for the help

 

[Jonmo1]

You're welcome.