Mass-change border hue

[juanito]

Hi - imagine we have a standard report in table format (just one sheet) with dark borders around many data groups. We thought that helped them stand out nicely, but now we have a new manager who wants to reduce "data-ink ratio": he wants us to keep all the same borders, but to change them to a light grey hue instead of their current black.


Can we change all the border colours from black to grey in one go, or do we need to click in each cell or range so that we're effectively redrawing all the same borders in the new hue (rhyme not intended.. and probably doesn't rhyme with Hui anyway...)?

 

[fred]

If all the cells border format remain the same, I would


- select all the cells involved

- right click on the selected cells

- choose "format cells..."

- choose "Border" tab

- under "Line" on the left hand side of the screen you should see a pull down menu title: "Color:"

- Click on the pull down menu and you should be able to choose a new color.


Fred

 

[juanito]

Fred - thanks for your response but it doesn't work!

 

[juanito]

I should add that this isn't a big deal for me - it's more of a theoretical issue: one of those moments when you think, "there must be a quicker way to do this".

- Juanito

 

[aarthimaa]

Select the Table and then click the FORMAT AS TABLE button in STYLES under HOME tab. Then, choose the type you want.

 

[vijaySharma]

John,


Just 1 more thing to add to what Fred has suggested above... after you have selected the new color


You will need to apply this to the relevant sections of the Table by clicking the relevate buttons for the table borders.


However; my guess on this topic was you were looking for an option to automatically update the colors on the tables.


Let me know if i was thinking in the right direct; and what is the version in which the files are created.


If XL2003 and below;

1- we may need to write VBA code to accomplish this.

2- We can try to change the default 56 color pallette to the one's that we need and they will be applied to the respective areas.


In XL2007 there is a Theme feature which when used and changed auto applies the changes acorss the areas where it has been used.


~Vijay

 

[juanito]

Guys: when I said table I was referring to the layout of the report - not Table in the technical excel sense.


Vijay, imagine this: cell A1 has a left-side red border, and cell A2 has a right-side red border. How can I change both border colours in one go?


- Juanito

 

[vijaySharma]

John,


If the workbook has been created in Excel 2003; we can easily modify the Default Color Palette to accomplish this.


Open the workbook

Go to Tools... Options... and then Click on the Color Tab


Click on the color that you wish to change and then click on Modify.

Now select the new color and then click on Ok


Note: This change of color will spread across the sheet; so where ever the old color was used it will be auto replaced with the new color.


I am trying to figure out how to do this with 2007 and will post on the same shortly.


~Vijay

 

[juanito]

Vijay - yes, but what if my two cell borders are coloured red by selection not by default? How can I change these two borders (the example is trivial but should be illustrative) in one go?

 

[vijaySharma]

John,


We would need to write VBA code to check the below


Ask / display the user a color palette to chose the Original Color


Ask if this is for the whole sheet OR for a specific Range


then search for the color using the below


Application.FindFormat.Borders(xlEdgeRight).Color

Application.FindFormat.Borders(xlEdgeLeft).Color

Application.FindFormat.Borders(xlEdgeTop).Color

Application.FindFormat.Borders(xlEdgeBottom).Color


Once found


Replace the color using the below code


Application.ReplaceFormat.Borders(xlEdgeLeft).Color

Application.ReplaceFormat.Borders(xlEdgeTop).Color

Application.ReplaceFormat.Borders(xlEdgeBottom).Color

Application.ReplaceFormat.Borders(xlEdgeRight).Color


Sounds simple; however it will take a effort to write this code... I will try to complete this today if possible.


~Vijay

 

[juanito]

Vijay - don't trouble with writing the code: all I wanted to see was whether this apparently simple operation could be done with the standard UI... looks like it can't which I find odd but no big deal. Thanks