Macro to change cell borders

[Guest]

I have a large number of sheets with thick borders on some cells. I need to
change the borders to thin. It seems to me that this is something which could
be automated with a macro - checking each cell and changing any borders that
exist.

I have written simple macros in the past but this is beating me. Could
someone point me in the right direction.

Thanks

 

[Guest]

Sub yay()
For Each h In Sheet1.UsedRange.Cells
If h.Borders(xlDiagonalUp).Weight <> xlThin Then
h.Borders(xlDiagonalUp).Weight = xlThin
If h.Borders(xlDiagonalDown).Weight <> xlThin Then
h.Borders(xlDiagonalDown).Weight = xlThin
If h.Borders(xlEdgeLeft).Weight <> xlThin Then h.Borders(xlEdgeLeft).Weight
= xlThin
If h.Borders(xlEdgeRight).Weight <> xlThin Then
h.Borders(xlEdgeRight).Weight = xlThin
If h.Borders(xlEdgeTop).Weight <> xlThin Then h.Borders(xlEdgeTop).Weight =
xlThin
If h.Borders(xlEdgeBottom).Weight <> xlThin Then
h.Borders(xlEdgeBottom).Weight = xlThin
If h.Borders(xlInsideHorizontal).Weight <> xlThin Then
h.Borders(xlInsideHorizontal).Weight = xlThin
If h.Borders(xlInsideVertical).Weight <> xlThin Then
h.Borders(xlInsideVertical).Weight = xlThin
Next
End Sub
try that sub i tested it and it worked

 

[Guest]

Excellent Ben. Many thanks!

Roddie

 

[Tom Ogilvy]

Here is another possibility

Sub yay()
For Each h In ActiveSheet.UsedRange.Cells
For i = 5 To 10
If h.Borders(i).Weight <> xlThin Then _
h.Borders(i).Weight = xlThin
Next
Next
End Sub

 

[Guest]

ah thanks tom, i always forget that all xl constants also have a numeric twin

 

[Guest]

Many thanks Tom

Roddie

Here is another possibility

Sub yay()
For Each h In ActiveSheet.UsedRange.Cells
For i = 5 To 10
If h.Borders(i).Weight <> xlThin Then _
h.Borders(i).Weight = xlThin
Next
Next
End Sub