Vba to change all borders on sheet

[tonywatsonhelp]

Hi Everyone,

I have a sheet with a lot of boarders done by someone else, who has a ver different idear of style to me,

So i'm left with a page that is set out OK but the boarders are ugly,

So what i Need if iits posible is this

The Range is A1:Z4000

I need a macro that when run will change all existing boarders to Thin and colour RGB 255,0,0

is this posible?

please help if it is

Thanks

Tony

 

[yky]

Record a macro and you'll have it.

 

[tonywatsonhelp]

Hi Thanks for the help but if i record a macro i'll need to replace every boarder manually! I'm not sure you understood my problem,
Let me try again,

Imagine a page with lots of boarders in different sizes covering small parts of the page and in lots of thicknesses and colours,

what i need is a macro that can go for each cell in range A1:Z4000

if cell has Right boarder replace with boarder RGB 255,0,0, thin line,
if cell has left boarder replace with boarder RGB 255,0,0, thin line,
if cell has top boarder replace with boarder RGB 255,0,0, thin line,
if cell has bottom boarder replace with boarder RGB 255,0,0, thin line,

so the macro is going over the page and changing any boarders that are already there with a nice thin consistant look.

Hope thats clears things up,
Again, please help if you Can!

Thanks

Tony

 

[yky]

OK, I see it now. It can be done. I'll do it tonight if no one solves it by then.

 

[tonywatsonhelp]

Thank you

 

[yky]

See if this works for you.

Sub border_change()
'change existing borders to xlThin
'

Dim rng1 As Range
Dim cell As Range

Application.ScreenUpdating = False

Set rng1 = ThisWorkbook.Sheets("Sheet1").Range("A1:Z4000")


For Each cell In rng1
    With cell.Borders(xlEdgeLeft)
    If cell.Borders(xlEdgeLeft).Weight <> xlThin Then
   
        .LineStyle = xlContinuous
        .Color = RGB(255, 0, 0)
        .TintAndShade = 0
        .Weight = xlThin
        End If
    End With
    With cell.Borders(xlEdgeTop)
    If cell.Borders(xlEdgeTop).Weight <> xlThin Then
        .LineStyle = xlContinuous
        .Color = RGB(255, 0, 0)
        .TintAndShade = 0
        .Weight = xlThin
        End If
       
    End With
    With cell.Borders(xlEdgeBottom)
    If cell.Borders(xlEdgeBottom).Weight <> xlThin Then
        .LineStyle = xlContinuous
        .Color = RGB(255, 0, 0)
        .TintAndShade = 0
        .Weight = xlThin
        End If
       
    End With
    With cell.Borders(xlEdgeRight)
    If cell.Borders(xlEdgeRight).Weight <> xlThin Then
        .LineStyle = xlContinuous
        .Color = RGB(255, 0, 0)
        .TintAndShade = 0
        .Weight = xlThin
        End If
       
    End With
   
    Next cell
   
    Application.ScreenUpdating = True
   
End Sub

 

[tonywatsonhelp]

Hi yky,
this will be a great help, thank you for this
Tony

 

[yky]

Glad to be able to help and thanks for the feedback.