Tukukkk

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)

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

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