Set default border color for all new and existing sheets

Hello,

I have tried my best googling for an adequate solution to this, but have repeatedly come up empty-handed. So I lay myself once again at the mercy of giants.

I like my cell borders (not the gridlines, to be clear) to be a light gray color instead of black. I never use black borders. I would like to set the borders to gray as a default so that all new and existing sheets use gray borders from now on.

I have tried creating a custom style, saving my preference to book and sheet templates, using the macro recorder, and still I have come up empty handed. The closest I have come is through using this macro:

Please Login or Register  to view this content.

This works pretty well, but it can take a while depending on the range selected, and I have to manually do it for each sheet. I also worry (though have not confirmed) that if I try to do this for an entire sheet by selecting the entire sheet's range, then it will program that preference into each cell, increasing the workbook size unnecessarily.

Is there a macro I can run on workbook open (perhaps that I can put in the personal.xlsb file?) that will set the border line to gray for all future borders? Or is there a simpler way that I have simply missed?

Thank you in advance.
Pat

Not sure if this could be of help as I could not run your maco and make a comparasing with what I use, still here it is and perhaps it could b of use.

Please Login or Register  to view this content.

Alf

Alf,

Thank you for your help, but the macro that you provided me puts a border around every cell in a range, where my goal was to set the color of borders going forward (not for every cell now). Moreover, setting your macro to a theme color does not make that color the default color, which is what I am trying to do.

Again, to rephrase, I would like to set the border default to gray for all future borders. This does not mean that I want every cell to have a gray border, but that I want the border to be gray when I put borders around a cell or a range of cells.

Perhaps the code I included led to some confusion. This was a workaround for me to set border color after the fact, when I would much prefer to set the border in advance, and not have to worry about going in and changing it.

Best,
Pat

puts a border around every cell in a range,

Why not combine your code and mine i.e. use your setup and just replace the

Please Login or Register  to view this content.

2

with

Please Login or Register  to view this content.

and see if this runs faster than your original code. By the way "ThemeColor" value seems to be defined for the rang 1 to 12.

Have you considered settin one cell with the borders you you would like to use , copy that cell and pasting that cell formate to the range of cells you wish to give the same border? Perhaps this would be a faster way of doing it?

Alf

Alf,

The if statements identify whether a cell has automatic borders around it, and then change the color of those borders. They do not apply a gray border to all four sides of every cell in the range. So, if I remove the if statements, then the macro places all four borders around each cell in the specified range. I'm trying to find all the borders within a specified range and change their colors to gray.

I tried replacing the .colorindex = 15 with .themecolor = 4 together with a timer macro (I don't think I'm allowed to cross-link but one could search "creating a timer for macro code execution") and found that the difference in duration was insignificant. Another obstacle I found was that I have my own theme colors and the light gray I want is not one of those colors, so I need to use colorindex to get the gray I want.

I tried your suggestion of setting one cell with gray borders, pasting the format to another range of cells, and erasing the borders (since I don't want all my cells to have borders), but erasing the borders resets the cells to automatic.

Again, the main goal and hope here is to change the default borders for all sheets and for all workbooks in the future. In other words, when I go to format cells->borders, I would like the color of the borders to be gray.

Thanks again,
Pat