Macro for Borders and Colouring

[AnthonyWB]

Hello everyone. I wanted to add some finishing touches to an automation project I have been working on, I recently recorded a macro which adds colors and fonts and borders and so fourth to a worksheet. Is there a better way to code this. It works perfectly fine but I was hoping for something cleaner and tighter. Thanks

Please Login or Register  to view this content.

[pike]

Hi AnthonyWB,
You could replace "select" with "With" to speed things up by not needing to select them

"

Please Login or Register  to view this content.

[JBeaucaire]

The "Bloat" that comes from recorded code is worst of all in the area of cell formatting. The best time to edit the code is immediately after you record it when what you changed in the cell is freshest in your mind. Any code that refers to parameters you didn't set are unnecessary and can deleted, frequently leaving only a single line of code in place of the 10-15 lines that got recorded.

Here I tried to guess at which settings represented "defaults" and removed all of those. Along with Pike's removal of selecting this greatly increases the efficiency of your code.

Please Login or Register  to view this content.

Just something to look at...I may have taken too much out, which is why the best time to edit is right after recording. You would know the only thing you did was add a TintShade so you would know all the rest was dross.


TIP: When editing border code, xlContinuous is the default linestyle, so just setting the weight will set that as well.