Border Color

[Manhar]

I have an excel sheet containing several borders with
varying line styles. The color of these is automatic
(black). I wish to change to 50% grey. The
format/cell/border menu does not appear to allow
wholesale change of color. Changing them individually is
not an option, since it would take a long time. Please
help!

 

[Bob Phillips]

Manhar,

Here is some code to do it.

Select all your cells, then run this code

With Selection
.Borders(xlEdgeLeft).ColorIndex = 48
.Borders(xlEdgeTop).ColorIndex = 48
.Borders(xlEdgeBottom).ColorIndex = 48
.Borders(xlEdgeRight).ColorIndex = 48
.Borders(xlInsideHorizontal).ColorIndex = 48
.Borders(xlInsideVertical).ColorIndex = 48
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[David McRitchie]

Doesn't work for me changed all cell borders to a
consistent line thickness with a gray border. Poster has
borders of various thicknesses and possibly not all cells
have borders. As I read it *only* the color of existing borders
should be changed.
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

Select all your cells, then run this code

With Selection
.Borders(xlEdgeLeft).ColorIndex = 48
.Borders(xlEdgeTop).ColorIndex = 48

[clipped]

 

[Simon Shaw]

You could cycle through the cell(s) selected and evaluate the borders, then change them as required, i.e.

if ActiveCell.Borders(xlEdgeLeft).ColorIndex = 30 Then ActiveCell.Borders(xlEdgeLeft).ColorIndex = 4

You could also test for line thickness

----- David McRitchie wrote: ----

Doesn't work for me changed all cell borders to
consistent line thickness with a gray border. Poster ha
borders of various thicknesses and possibly not all cell
have borders. As I read it *only* the color of existing border
should be changed
--
HTH
David McRitchie, Microsoft MVP - Exce
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.ht

Select all your cells, then run this cod
.Borders(xlEdgeLeft).ColorIndex = 4
.Borders(xlEdgeTop).ColorIndex = 4

[clipped