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

Question

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)

Answer 1

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