vba 范围内每个单元格的边框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13121425/
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
Border around each cell in a range
提问by CustomX
I am trying to create a simple function that will add borders around every cell in a certain range. Using the wonderful recording this generates a ton of code which is quite useless. The code below will display a 'table' of data, around each cell in this range I would like to add a border. Online I haven't been able to find a simple or clear answer for this.
我正在尝试创建一个简单的函数，它将在特定范围内的每个单元格周围添加边框。使用精彩的录音，这会生成大量无用的代码。下面的代码将显示一个数据“表格”，我想在这个范围内的每个单元格周围添加一个边框。在网上我一直无法找到一个简单或明确的答案。
All help is much appreciated!
非常感谢所有帮助！
Set DT = Sheets("DATA")
endRow = DT.Range("F" & Rows.Count).End(xlUp).Row
result = 3
For I = 2 To endRow
If DT.Cells(I, 6).Value = Range("B1").Value Then
Range("A" & result) = DT.Cells(I, 6).Value
Range("B" & result) = DT.Cells(I, 1).Value
Range("C" & result) = DT.Cells(I, 24).Value
Range("D" & result) = DT.Cells(I, 37).Value
Range("E" & result) = DT.Cells(I, 3).Value
Range("F" & result) = DT.Cells(I, 15).Value
Range("G" & result) = DT.Cells(I, 12).Value
Range("H" & result) = DT.Cells(I, 40).Value
Range("I" & result) = DT.Cells(I, 23).Value
result = result + 1
End If
Next I
回答by Jon Crowell
You only need a single line of code to set the border around every cell in the range:
您只需要一行代码即可设置范围内每个单元格的边框：
Range("A1:F20").Borders.LineStyle = xlContinuous
Range("A1:F20").Borders.LineStyle = xlContinuous
It's also easy to apply multiple effects to the border around each cell.
对每个单元格周围的边框应用多种效果也很容易。
For example:
例如：
Sub RedOutlineCells()
Dim rng As Range
Set rng = Range("A1:F20")
With rng.Borders
.LineStyle = xlContinuous
.Color = vbRed
.Weight = xlThin
End With
End Sub
回答by Olle Sj?gren
The following can be called with any range as parameter:
可以使用任何范围作为参数调用以下内容：
Option Explicit
Sub SetRangeBorder(poRng As Range)
If Not poRng Is Nothing Then
poRng.Borders(xlDiagonalDown).LineStyle = xlNone
poRng.Borders(xlDiagonalUp).LineStyle = xlNone
poRng.Borders(xlEdgeLeft).LineStyle = xlContinuous
poRng.Borders(xlEdgeTop).LineStyle = xlContinuous
poRng.Borders(xlEdgeBottom).LineStyle = xlContinuous
poRng.Borders(xlEdgeRight).LineStyle = xlContinuous
poRng.Borders(xlInsideVertical).LineStyle = xlContinuous
poRng.Borders(xlInsideHorizontal).LineStyle = xlContinuous
End If
End Sub
Examples:
例子：
Call SetRangeBorder(Range("C11"))
Call SetRangeBorder(Range("A" & result))
Call SetRangeBorder(DT.Cells(I, 6))
Call SetRangeBorder(Range("A3:I" & endRow))
回答by Crazyd
I have a set of 15 subroutines I add to every Coded Excel Workbook I create and this is one of them. The following routine clears the area and creates a border.
我将一组 15 个子例程添加到我创建的每个编码的 Excel 工作簿中，这就是其中之一。以下例程清除该区域并创建一个边界。
Sample Call:
示例电话：
Call BoxIt(Range("A1:z25"))
Subroutine:
子程序：
Sub BoxIt(aRng As Range)
On Error Resume Next
With aRng
'Clear existing
.Borders.LineStyle = xlNone
'Apply new borders
.BorderAround xlContinuous, xlThick, 0
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlMedium
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlMedium
End With
End With
End Sub
回答by Dick Kusleika
Here's another way
这是另一种方式
Sub testborder()
Dim rRng As Range
Set rRng = Sheet1.Range("B2:D5")
'Clear existing
rRng.Borders.LineStyle = xlNone
'Apply new borders
rRng.BorderAround xlContinuous
rRng.Borders(xlInsideHorizontal).LineStyle = xlContinuous
rRng.Borders(xlInsideVertical).LineStyle = xlContinuous
End Sub
回答by Madjid Sepanj
xlWorkSheet.Cells(1, 1).Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlDataBarBorderType.xlDataBarBorderSolid
xlWorkSheet.Cells(1, 1).Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlDataBarBorderType.xlDataBarBorderSolid
xlWorkSheet.Cells(1, 1).Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlDataBarBorderType.xlDataBarBorderSolid
xlWorkSheet.Cells(1, 1).Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlDataBarBorderType.xlDataBarBorderSolid
回答by Mariusz Krukar
You can also include this task within another macro, without opening a new one:
您还可以将此任务包含在另一个宏中，而无需打开新的宏：
I don't put Sub and end Sub, because the macro contains much longer code, as per picture below
我没有把 Sub 和 end Sub 放在一起，因为宏包含更长的代码，如下图所示
With Sheets("1_PL").Range("EF1631:JJ1897")
With .Borders
.LineStyle = xlContinuous
.Color = vbBlack
.Weight = xlThin
End With
[![enter image description here][1]][1]End With
回答by Sylca
For adding borders try this, for example:
添加边框试试这个，例如：
Range("C11").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("A15:D15").Borders(xlEdgeBottom).LineStyle = xlContinuous
Hope that syntax is correct because I've done this in C#.
希望语法是正确的，因为我已经在 C# 中完成了这项工作。
