Vba all round border code

[ipbr21054]

Hi,
Can you advise please why the border around the whole cell isnt applied when i past a value please.

    Target.Interior.Color = vbGreen
    Else
    Target.Interior.Color = vbRed
    End If
    With ActiveSheet.Range("E4:E28")
        .Font.Size = 11
        .Font.Bold = True
        .Font.Color = vbBlack
        .Font.Name = "Calibri"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        Range("E4:E28").BorderAround xlContinuous, xlThin
        Range("E4:E28").NumberFormat = "@"
    End With
    Application.ScreenUpdating = True

End Sub

 

[MARK858]

What happens if you just use

Range("E4:E28").BorderAround xlContinuous

 

[ipbr21054]

Hi,

Same please see screen shot

I recorded a Macro which worked so can we make this code smaller.

    Range("B12").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("B13").Select
End Sub

 

[ipbr21054]

This did it.

Range("B4:E28").Borders.LineStyle = xlContinuous

 

[MARK858]

Happy that you got a solution that works for you (although it isn't what I interpreted by your question i.e. border around).

Please note that the way you have it written you won't have the option of the weight and the linestyle. It is one or the other.

 

[ipbr21054]

Hi,
So does that mean we need to continue with another code and forget what i put above ?

 

[MARK858]

Depends what you want it to do.

 

[ipbr21054]

Hmmm
What ive done matches the others so i best leave it but thanks anyway.

 

[MARK858]

When using borders the default linestyle is xlcontinuous and the default weight is xlthin so in your case you could use any of the following

Range("B4:E28").Borders.LineStyle = xlContinuous
Range("B4:E28").Borders.Weight = xlThin
Range("B4:E28").Borders.ColorIndex = 0

and you would get the same result.

You can also specify one change, for example

Range("B4:E28").Borders.Weight = xlThick

and have no issues as the linestyle and color will use the defaults.

What you couldn't do is use the same syntax if you wanted 2 options different to the default. For example if you wanted a xlSlantDashDot linestyle and medium borders then you would have to use something like

    With Range("B4:E28")
        With .Borders
            .LineStyle = xlSlantDashDot
            .Weight = xlMedium
        End With
    End With

or with diagonals

    With Range("B4:E28")
        With .Borders
            .LineStyle = xlSlantDashDot
            .Weight = xlMedium
        End With
        With .Borders(xlDiagonalDown)
            .LineStyle = xlSlantDashDot
            .Weight = xlMedium
        End With
        With .Borders(xlDiagonalUp)
            .LineStyle = xlSlantDashDot
            .Weight = xlMedium
        End With
    End With

Please note that

Range("B4:E28").Borders.LineStyle = xlContinuous

does not change any diagonal borders, you have to set them separately if you ever need them (see code above).

 

[ipbr21054]

Thanks for this reply.
As you made the time to advise this I will now then use it.
Many thanks.

Do you have time to take a glance at my other post I’m working on.
At a point now where I’m stumped.

Thanks again.