Identify cells with color fill and borders

[Ken Puls]

Hey guys,

I'm trying to loop through each worksheet in a workbook, copying the color indexes and borders from any cells that have them to another workbook with an indetically named worksheet. I have comitted the ultimate sin in trying to do this with a loop, but unfortunately the workbook has 261 worksheets, and some are over 10,000 rows each. (Please no lectures on workbook design, I'm doing this for someone else.)

The thing is that I ONLY want the cells.interior.colorindex and the cell borders, and I don't want anything else.

Is there a way to identify/copy these things quickly?

Thanks,

[austenr]

Hi Ken


What Color is used in font, interior and borders

Dont know if this will get you going or not. Found with a search:
http://www.pvv.ntnu.no/~nsaa/excel.html




Might have to modify it to suit your needs.

[Zack Barresse]

How many times is this supposed to be done Ken? If it's just once, a loop doesn't sound out of the picture here. It may be a lot of sheets, but I'm sure you can streamline a loop to run efficiently.

[Ken Puls]

LOL,

I've streamlined it the best I can, but it still needs to look at every cell to see if there is a colorindex and/or borders, then copy them to the new sheet.

With 261 sheets, lots of columns on each, and tons of rows... It's been running since last night at 11:00PM, (Pacific). As of Noon (13 hours), it was almost half way done.

I'm waiting for Dave to come call me a sinner, but I have no idea how to thin down those ranges any more? Specialcells has something for conditionals, but nothing for just plain formatted cells.

[Zack Barresse]

I wonder if it wouldn't be faster to use the GET.CELL method. Paddy's post at MrE does a fine explanation of this.. then you could peruse only those cells in a seperate column of formulas which meet your specifications. Could speed it up plenty if you set it up right (I would think - without seeing the data of course).

Thread referred to is here: http://www.mrexcel.com/board2/viewtopic.php?t=21312

[Ken Puls]

Hey Zack,

It's interesting, but wouldn't you still have to do the same? Loop through each cell, querying if it matched a criteria and then act? That's essentially what I've doing now.... did I miss something?

[Zack Barresse]

Yes, but you could trim it down drastically. Let's say you built a couple of extra columns on each sheet (done easily enough by selecting all sheets and entering your formula at once, confirmed with Ctrl + Enter). I'll say for now that you are using a column to test the entire row for any conditions met. Let's also say that you used an IF function to represent all those with matching criteria to leave a 1 and those that do not to leave a 0. You could then autofilter for 1 and use special cells to loop through just the rows which have matching criteria somewhere in them.

This of course is really dependent upon data structure and what you're looping through. It may - or very well may not - save you time. But the picture in my mind, well, isn't always right, but may save you time.

[Ken Puls]

Ahhh! Okay, I think I see where you're going with this. Yes, don't know if it would work, but is certainly worth a look. I think I'm going to let it run the rest of the 7-10 hours it needs to complete (I wouldn't want to have to do it again), but it might be helpful to try it for future.

Thanks!

[Zack Barresse]

If you know where it's at in the process, you can always adjust the loop(s) to start there .. 7 to 10 hours is a long time. I'm thinking you could beat that.

[Ken Puls]

LOL!

I could, but I have so much invested in it at this point that I'm afraid to touch it!

[Zack Barresse]

Since I can't say it well enough myself, I'll let somebody else say it ...

The most important thing is to not stop questioning.

Problems cannot be solved at the same level of awareness that created them.

[johnske]

Ken,

I understand that data and formulas aren't to be copied, and naturally I assume you don't want names, validation, conditional formats and comments copied either, but each cell has a format that includes borders and colour, so can the rest of these formats be copied (e.g. "General", "Number" etc)?

If so, do a paste special (formats) to a temporary sheet (names, validation and comments won't be copied), delete all conditional formats with [vba]Cells.FormatConditions.Delete[/vba] on the temp sheet, then copy this altered temp sheet and paste special it (formats again) to where ever you want.

????
John

[TonyJollans]

I looked at this and decided there were too many (possible) variables - a general solution would be interesting but for a one-off it's not worth the effort and I would just let the simple loop run the day or two it takes.

The trouble with what you suggest, John, is that it will overwrite all existing formats on the target sheet.

[Ken Puls]

I looked at this and decided there were too many (possible) variables - a general solution would be interesting but for a one-off it's not worth the effort and I would just let the simple loop run the day or two it takes.

Yep. I worked on other stuff on my wife's PC and just let her go. At least we have two! All in all, it took 32 mind boggling hours to complete on a 3.06Ghz with 512MB RAM. Yikes!

The trouble with what you suggest, John, is that it will overwrite all existing formats on the target sheet.

Indeed. I needed to make sure that the rest of the formats weren't touched. Only colorindexes and borders.

Zack, I may still try your suggestion, more for curiosity at this point than anything else.

Thanks!

[Cyberdude]

I can't quite get it straight iin my mind, but I can't halp but wonder if it might get done quicker if you broke it down into multiple copy sessions. You know, maybe you're getting virtual storage thrashing or something. Can you say with any certainty that dividing it into, say, 5 runs instead of one will wind up with the same overall elapsed time of execution?? (Just thnking out loud.)

[Zack Barresse]

I wouldn't think so, Sid. Not if running the same code. The only thing you'd be doing there is making it compile 5 times instead of one, and creating yourself more overhead. Unless the code efficiency was directly changed, I can't see any difference except negative.