Slow VBA code: Protecting cells based on colors while changing colors based on cell change

[shoren]

Hello! Have been reading these forums for weeks while doing some work and the helpfulness is really endless! I have found helpful bits of code that works really really well, but it's very slow! It seems like it needs to execute after EVERY change to the worksheet in order to work.

This code, firstly, protects the worksheet and locks those cells that are color-coded other than green and yellow.

Then, it checks if there is a change to the cell and if there is a change to a cell it recolors the cell as yellow.

It runs very fast in a small worksheet with no data, but once I use the full Worksheet with 5 color-codes and up to 2000 rows it becomes very slow.

Is there a faster solution, like if I limited the working range of the sheet? I really only need to protect BM2:DC2000, not the whole thing.

Thank you for any ideas.

Please Login or Register  to view this content.

[gmr4evr1]

Add...

Please Login or Register  to view this content.

After your sub title
and

Please Login or Register  to view this content.

Before End Sub
See if that helps.

[shoren]

That did it! Thank you so much. I know I probably should have read a FAQ about this out there somewhere on optimizing your code, appreciate it tons!

[gmr4evr1]

No problem, glad it worked out for you.