Border for cells within range not working

[gdev]

Hi,
Please assist.

I have a thick border around range and range a5:l8
I want thin borders around cells in range from a5 to the end of range.

The below code gives me the thick borders I want not the cells within the range.

Set rngAL8 = Range(Cells(Rows.Count, "A").End(xlUp), Range("l5"))

With rngAL8
'.Borders.LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).Weight = xlHairline

.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlHairline

.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlHairline

.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideVertical).Weight = xlHairline
End With


Set rng = Range(Cells(Rows.Count, "A").End(xlUp), Range("l1"))
With rng
.Borders(xlInsideHorizontal).LineStyle = xlNone
.Borders(xlEdgeLeft).Weight = xlThick
.Borders(xlEdgeRight).Weight = xlThick
.Borders(xlEdgeTop).Weight = xlThick
.Borders(xlEdgeBottom).Weight = xlThick
End With

Set rnga5l7 = Range("A5:L7")
With rnga5l7
.Borders(xlInsideHorizontal).LineStyle = xlNone
.Borders(xlEdgeLeft).Weight = xlThick
.Borders(xlEdgeRight).Weight = xlThick
.Borders(xlEdgeTop).Weight = xlThick
.Borders(xlEdgeBottom).Weight = xlThick
End With

Thanks

 

[SkipVought]

Hi,

    With Range("a5:l8")
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThick
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThick
        End With
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThick
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThick
        End With
    End With

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[AnotherHiggins]

I like making it as short as possible.

First, remove unnecessary border specifications. If you just define Borders without putting anything in parentheses, Excel understands to apply the settings to all interior and exterior borders (but not diagonals).

Next, remove LineStyle, because it already defaults to xlContinuous. And assuming you want black borders, you can omit ColorIndex, as it defaults to xlAutomatic.

So you are left with the following:

[green]' Set ALL borders to xlMedium (or xlThick if you want)[/green]
Selection.Borders.Weight = xlMedium

[green]' Now Select the area(s) that you want to have thinner vertical/horizontal borders[/green]
[green]' and set [b]ONLY[/b] those borders to xlThin (or xlHairline)[/green]
Selection.Borders(xlInsideVertical).Weight = xlThin
Selection.Borders(xlInsideHorizontal).Weight = xlThin

So basically you set all borders to xlmedium (or xlthick), then select the areas where you want thinner interior borders and for those areas ONLY define interior borders.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[gdev]

Thanks, Thanks!
It works beautifully