Borders Macro runtime error Need Help

[danjw_98]

I am using the below macro to create borders on multiple sheets but it gives me a runtime error 1004 "unable to set the weight property of the border class". I thought if i removed them first it would fix the problem but it didn't. Any help would be appreciated...thanks..


Sub FormatBorders()
Application.ScreenUpdating = False
'On Error Resume Next
Dim wrkSheet As Worksheet

For Each wrkSheet In Sheets(Array("Sheet1", "sheet2", "sheet3"))
With wrkSheet

lastrow = .Range("c65536").End(xlUp).Row
For x = 2 To lastrow

Set rng = .Range("A" & x & ":r" & x)

With rng
.Borders.LineStyle = xlNone
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlInsideVertical).Weight = xlThin
End With


Next x
End With
Next wrkSheet

Application.ScreenUpdating = True

End Sub

 

[Andrew Poulsom]

Which line is causing the error?

 

[danjw_98]

i have gone thru and x'd out each one and still get the error except the top line runs. thanks for looking at this...


.Borders.LineStyle = xlNone
'.Borders(xlEdgeTop).Weight = xlThin
'.Borders(xlEdgeBottom).Weight = xlThin
'.Borders(xlEdgeLeft).Weight = xlThin
'.Borders(xlEdgeRight).Weight = xlThin
'.Borders(xlInsideVertical).Weight = xlThin

 

[Andrew Poulsom]

Your code worked perfectly for me in Excel 2003. What version of Excel are you using?

 

[danjw_98]

I am using 2007 but the workbook is in 2003 compat mode.

 

[Andrew Poulsom]

I have no problem with that scenario either. How may cells are you formatting on each worksheet? The worksheets aren't protected are they?

 

[danjw_98]

there is no protection on the workbook or sheets and the number of rows would vary (adding info).

 

[Andrew Poulsom]

How many cells are being formatted when it fails?