CODEWITHSUNDEEP
vbaexcel
brettarded
brettarded

Reputation: 43

Change Border Color of a Range Without Changing the Linestyle/Weight

I have a nicely formatted range of cells with different border line weights (some of them are medium thickness and some of them are thin, in no particular pattern). I want to run a macro that changes the color of the borders to grey, but every time I do it, it changes all of the border weights to xlThin automatically. I want to keep the original line thickness so I don't have to go through and change the respective ones back to xlMedium, which would be tedious.

Can someone help me out? Is this possible?

The code I currently have is simple, and it changes the color correctly. It just also changes the line weight automatically, even though I don't specify the weight or linestyle at all: Range("NamedRange").Borders.Color = RGB(150, 150, 150)

Upvotes: 4

Views: 34125

Answers (3)

Louis
Louis

Reputation: 86

To change the cell border color in a loop, using the enum value for each border makes it easy to loop through them.

This code will change the border color of the selected cell. If there's no line the MsgBox will indicate its value.

Sub CellBorderColour()

Dim MyBorder(5 To 12) As String
Dim i As Integer

MyBorder(5) = "xlDiagonalDown"
MyBorder(6) = "xlDiagonalUp"
MyBorder(7) = "xlEdgeLeft"
MyBorder(8) = "xlEdgeTop"
MyBorder(9) = "xlEdgeBottom"
MyBorder(10) = "xlEdgeRight"
MyBorder(11) = "xlInsideVertical"
MyBorder(12) = "xlInsideHorizontal"

For i = 5 To 12
    With Selection.Borders(i)
        If .LineStyle > 0 Then
            .Color = RGB(100, 100, 100)
        Else
            MsgBox ("Borders(" & MyBorder(i) & ").LineStyle is: " & .LineStyle)
        End If
    End With
Next i
End Sub

Upvotes: 0

Gapus
Gapus

Reputation: 1

Try .Borders.Color = RGB(216,216,216)

I ran the below script to try to identify the closest color to normal gridlines. My eyes are not great so check it out yourself to find the best color. BTW I agree it makes no sense that MS overrides the border color defying reason. Angry employees and too much bureaucracy - that's my theory.

Sub borcol()
    Dim i As Integer
    For i = 1 To 250
        ActiveCell.Borders.Color = RGB(i, i, i)
        ActiveCell.Offset(1, 0).Select
    Next i
End Sub

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

This, on my Excel 2016, will only change the color of the cell border, without changing the size:

Sub changeColorOnly()
Dim rng As Range, cel As Range
Set rng = Range("C6:C9")

For Each cel In rng
    cel.Borders.Color = RGB(150, 150, 150)
Next cel

End Sub

Does it still change the size for you?

Edit: Hm, I suspect there's something else going on in your code, as I can recolor a named range without it affecting the borders. However, just because I was already working on another alternative, you could also use these subs (and tweak as necessary)

Dim brdrTop, brdrLeft, brdrRight, brdrBtm, brdrInside

Sub changeColor()
saveBorderSize Range("myNamedRange")
Range("MyNamedRange").Borders.Color = RGB(150, 150, 150)
resetBorderSize Range("myNamedRange")
End Sub

Private Sub saveBorderSize(cel As Range)
brdrTop = cel.Borders(xlEdgeTop).Weight
brdrLeft = cel.Borders(xlEdgeLeft).Weight
brdrRight = cel.Borders(xlEdgeRight).Weight
brdrBtm = cel.Borders(xlEdgeBottom).Weight
brdrInside = cel.Borders(xlInsideHorizontal).Weight
End Sub

Private Sub resetBorderSize(cel As Range)
cel.Borders(xlEdgeTop).Weight = brdrTop
cel.Borders(xlEdgeLeft).Weight = brdrLeft
cel.Borders(xlEdgeRight).Weight = brdrRight
cel.Borders(xlEdgeBottom).Weight = brdrBtm
cel.Borders(xlInsideHorizontal).Weight = brdrInside
End Sub

Upvotes: 8

Related Questions