• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting works manually, but not if the sorting is in a macro

G

glennpc

Member
My worksheet is very large so I created a smaller dummy version to attach that works basically the same way.

I have a range with 12 rows-- with row 1 being a header row. There are 9 columns, one for a persons name and then Criteria-1 through Criteria 8.

I run conditional formatting -- 3 separate rules-- one for Criteria 1 = "ABC", flag it as bold red. One for Criteria 3, 4, and 5 taken as a block-- if any cell in that range = "FFF", flag it as bold red. Then I run a rule on Criteria 7-- if "TTT", flag it as bold red. This flags all my incorrect cells for later fixing.

What I want a macro to do is to cycle through all the rows and if it sees that all the cells in that row are not in Bold RED, (in other words, not conditionally formatted), then it hides that row. This way I'm left with only things I need to fix.

I can run a manual sort to do this (I've done it on the attached sheet) and it seems to work-- it moves all the rows that have red in them to the top. From here, I'd need to hide the bottom rows and I'm good to go.

But I've tried to put this in a macro, to no avail. Is it because you can't test for conditional formatting in a cell using VBA because CF is not the same property as cell's text color?

Any insights?
 

Attachments

  • SORT-WORKS.xlsm
    14 KB · Views: 2
Hi !

For large data, faster - and easy - is to use an Excel basics like
an advanced filter using same criterias …

From your attachment :

Input1.jpg

Call Advanced Filter, filter in-place,
List range from A1 to I2, Criteria range from M1 to Q6, done !

And if you really need a code, just activate Macro recorder and redo …
​
 
What do you put in the Criteria Range? There are multiple criteria, different for each column. And in this case to make it easy, I used "ABC" for Criteria 1, "TTT" for Criteria 7 and so on. On my actual sheet (which is WAY too large to upload), its more complex because it compares each cell in the range to valid entries stored in another range and flags any that don't match. And really, what I'm looking for is all the ones that the Cond. Formatting flagged in bold red.
 

As explained in my previous post and you yet have the pic for criteria !

If a code checks each cell "color" - in fact redo same condition as CF -
the more rows the longer execution …

Anyway, see Conditional Formatting Colors …​
 
Last edited:
The Pearson link you sent has some very valuable code in it. I can use his function to find sum of cells in a row that have CF, put the sum in a helper column, and then filter out all the zero sums of that helper column to just show me the error rows. Great! Thanks for your help Marc.
 
One glitch on this: Pearson's notes and his functions work great if your conditional formatting is simple (doesn't use a formula). Mine does. Pearson says to correct this, you have to use absolute cell references in your condition formula. I did, and then my formula didn't work. I had to put my formula back to this: "=ISERROR(MATCH(1,--EXACT($B$8:$B$63,AX8),0))" -- I had changed the AX8 to $AX$8-- and that's what made my formula not work. So I'm still searching for a solution.
 

So there are two ways :

• reproducing test conditions like in CF

• avanced filter with same criterias as CF …
​
 
You must log in or register to reply here.
Back
Top