vba 在不改变线型/粗细的情况下更改范围的边框颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41445924/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Change Border Color of a Range Without Changing the Linestyle/Weight
提问by brettarded
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.
我有一个格式很好的单元格范围，具有不同的边界线粗细（其中一些是中等厚度，一些是薄的，没有特定的模式）。我想运行一个将边框颜色更改为灰色的宏，但每次执行此操作时，它都会自动将所有边框权重更改为 xlThin。我想保持原来的线条粗细，这样我就不必通过将各自的线条改回 xlMedium，这会很乏味。
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)
我目前拥有的代码很简单，它可以正确更改颜色。它也会自动更改线条粗细，即使我根本没有指定粗细或线条样式：
Range("NamedRange").Borders.Color = RGB(150, 150, 150)
回答by BruceWayne
This, on my Excel 2016, will only change the color of the cell border, without changing the size:
在我的 Excel 2016 上，这只会更改单元格边框的颜色，而不会更改大小：
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
回答by Gapus
Try .Borders.Color = RGB(216,216,216)
尝试 .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.
我运行了下面的脚本来尝试识别最接近普通网格线的颜色。我的眼睛不太好，所以自己检查一下，找到最好的颜色。顺便说一句，我同意 MS 覆盖无视边框颜色的原因是没有意义的。愤怒的员工和太多的官僚主义——这就是我的理论。
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
