Help Speed up this Loop?!?

[arleutwyler]

Hi all, I have successfully created a sub that looks for 7 particular colors and add a pattern style to those cells. The problem I have with what I have created is that it is slow... not crazy slow but slow enough to make the end user think that the program has frozen. Any help would be huge!

Please Login or Register  to view this content.

Thank you!
Alex

[billstpierre79]

I didn't test this but it should work. You were cycling through the data 7 times. This code will go through the data only once and check all of the criteria that you set.

Please Login or Register  to view this content.

[arleutwyler]

Thanks for the fast reply! I ran your code and it seems move just a touch faster... just a touch. I am pretty new to VBA and I don't know if there is a better way to do this... I am open to any suggestions! Lets make this faster!!!

Thank you again,
Alex

[billstpierre79]

That's as fast as its going to move. Its checking over 46000 cells. But if you want the end user to know that the program is still running try this code. It will display "Running Macro" in the status bar while the code is running.

Please Login or Register  to view this content.

[arleutwyler]

Hmm well maybe I need to go about this a different way.

Right now I am using event code (sheet code) to fill cells that are changed with different colors for everyday of the week. (below)

Please Login or Register  to view this content.

I am using this to keep track of date changes in our weekly tracker. Is there a way to modify this code to include creating a named range so that every time a cell is changed it 1. changes colors and 2. gets added to a named range (I.g. The named range would be based on the day the changed occurred Monday, Tuesday, etc...) I then could use those named ranges to add the pattern style to those cells (I think this would significantly faster than looping through all cells). I currently have a sub that sends the colored sheet with updates via Outlook to the appropriate team members that update our master tracker. I would like to use the code to add the pattern style to the cells that have already have been changed after they have been sent in to be updated. The idea is two fold, 1. the user making the updates can see which one were sent in to the person updating the Master New vs old and 2. for the person updating the master tracker to differentiate the old vs the new updates.

I hope this makes sense to someone out there

Thanks in advanced
Alex

[billstpierre79]

Try this

Please Login or Register  to view this content.

[AlphaFrog]

Another one.

Please Login or Register  to view this content.

[arleutwyler]

I see what you are doing with splitting the range but I need it to included A2-MM1200. I think trying to add the cell that change to a named range will still run faster then running through all of those cells. Any advice on how I could use the code I posted to do what I was talking about?

thanks again

[arleutwyler]

Thanks Again AlphaFrog!! you've come through again!!! this is super fast

[jindon]

How about

Please Login or Register  to view this content.