如果特定行上的活动单元格，Excel VBA 添加边框

Excel VBA add border if active cell on certain row

提问by AdRock

I am writing some VBA for a Gantt Chart spreadsheet.

我正在为甘特图电子表格编写一些 VBA。

I have 3 months worth of dates on row 5 and i can set the start date by entering a date in a cell which updates the whole sheet.

我在第 5 行有 3 个月的日期，我可以通过在更新整个工作表的单元格中输入日期来设置开始日期。

I have a range of cells for my chart L6:CZ42. For this range, if the cell in row 5 is the 1st of the month, every cell in that column will have a grey dotted left border and nothing on the right. This works how i want it to.

我的图表 L6:CZ42 有一系列单元格。对于此范围，如果第 5 行中的单元格是该月的 1 号，则该列中的每个单元格都将具有灰色虚线左边框，而右侧没有任何内容。这是我想要的方式。

The problem is that it adds a grey border to the top and bottom of the cell which is OK for rows 7 to 41, but with row 6 i want a black top border and for row 42 I want a black bottom border.

问题是它在单元格的顶部和底部添加了一个灰色边框，这对于第 7 行到第 41 行是可以的，但是对于第 6 行，我想要一个黑色的顶部边框，而对于第 42 行，我想要一个黑色的底部边框。

I added this section of code trying to sort this problem but the syntax is wrong checking if it's on row 6

我添加了这部分代码试图对这个问题进行排序，但语法错误检查它是否在第 6 行

' If this is the first row (6) in the range then
' add a black continuous border to the top
If Cells(6, i) Then
    With .Borders(xlEdgeTop)
        .ColorIndex = 1
        .Weight = xlThin
        .LineStyle = xlContinuos
    End With
End If

This is my whole code

这是我的全部代码

Sub Worksheet_Change(ByVal Target As Range)

Dim i As Long
Dim CuDate As Date


For i = 12 To 104
CuDate = Cells(5, i).Value

' Are we on the 1st day of the month
If Day(CuDate) = 1 Then
    With Range(Cells(6, i), Cells(42, i))
        ' If this is the first row (6) in the range then
        ' add a black continuous border to the top
        If Cells(6, i) Then
            With .Borders(xlEdgeTop)
                .ColorIndex = 1
                .Weight = xlThin
                .LineStyle = xlContinuos
            End With
        End If

        With .Borders(xlEdgeLeft)
            .ColorIndex = 15
            .Weight = xlThin
            .LineStyle = xlDot
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlLineStyleNone
        End With
    End With
Else
    With Range(Cells(6, i), Cells(42, i))
        ' If this is the last row (42) in the range then
        ' add a black continuous border to the bottom
        If Cells(42, i) Then
            With .Borders(xlEdgeBottom)
                .ColorIndex = 1
                .Weight = xlThin
                .LineStyle = xlContinuos
            End With
        End If

        With .Borders(xlEdgeLeft)
            .LineStyle = xlLineStyleNone
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlLineStyleNone
        End With
    End With
End If

Next
End Sub

回答by Jean-Fran?ois Corbett

This line does not do what you think it does: If Cells(6, i) Then

此行不会执行您认为的操作： If Cells(6, i) Then

It is equivalent to saying: If Cells(6, i).Value = True Then, i.e. "if the content of cell on row 6 and column ievaluates to Truewhen implicitly coerced to a Boolean, then", which clearly is not what you want at all. Instead, try:

它相当于说：If Cells(6, i).Value = True Then，即“如果第 6 行和列上的单元格的内容i计算为True隐式强制为布尔值时，则”，这显然根本不是您想要的。相反，请尝试：

If ActiveCell.Row = 6 Then

The same reasoning goes for If Cells(42, i) Thenfurther down in your code.

同样的推理也适用If Cells(42, i) Then于您的代码。

回答by JimmyPena

[Update:Jean-Fran?ois Corbett has corrected your code logic re: checking if the active cell is in row 6. But with the typo the code doesn't produce the top and bottom borders.]

[更新：Jean-Fran?ois Corbett 更正了您的代码逻辑：检查活动单元格是否在第 6 行。但由于拼写错误，代码不会生成顶部和底部边框。]

Your code does not compile. Please consider using Option Explicitat the top of your code modules. The only way I could duplicate your issue was by removing Option Explicit. I set up the VBA Editor so it automatically places Option Explicitat the top of new modules.

您的代码无法编译。请考虑Option Explicit在代码模块的顶部使用。我可以复制您的问题的唯一方法是删除Option Explicit. 我设置了 VBA 编辑器，让它自动放置Option Explicit在新模块的顶部。

The LineStyle Property must be one of the XlLineStyleconstants:

LineStyle 属性必须是XlLineStyle常量之一：

• xlContinuous
• xlDash
• xlDashDot
• xlDashDotDot
• xlDot
• xlDouble
• xlSlantDashDot
• xlLineStyleNone

• xl连续
• 破折号
• xlDashDot
• xlDashDotDot
• 点
• xl双
• xlSlantDashDot
• xlLineStyle无

In your code, you wrote xlContinuosnot xlContinuous. Once you make this correction the code should work.

在您的代码中，您编写了xlContinuos而不是xlContinuous。进行此更正后，代码应该可以工作。

Also, this is a minor point but technically the Worksheet_Change Event should be declared as follows:

此外，这是一个小问题，但从技术上讲，Worksheet_Change 事件应声明如下：

Private Sub Worksheet_Change(ByVal Target As Range)

Private Sub Worksheet_Change(ByVal Target As Range)

May I kindly suggest you take advantage of the built-in features of the VBA Editor as well as the help documentation?

我可以建议您利用 VBA 编辑器的内置功能以及帮助文档吗？