vba - 尝试根据单元格的值更改文本颜色时出现“Subscript out of range”错误

Question

我正在尝试创建一个宏，该宏将比较标记为“申请编号”和“PO＃”的两列中的单元格的值与上一行中的单元格。如果单元格相同，则我希望线条以相同的颜色突出显示。以下是我的代码:

Sub changeTextColor()

    Dim Color As Integer

    Color = 5

    'Get number of rows in the specified column
    RowsCount = Range("A1", Range("A1").End(xlDown)).Rows.Count

    Dim colReq As Range
Dim colPO As Range


With ActiveSheet.UsedRange.Rows(1)
    Set colReq = .Find(What:="Requisition Number", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    Set colPO = .Find(What:="PO #", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
End With


    'Select cell
    ActiveSheet.Cells(1, colReq.Column).Select

    'Loop the cells
    For x = 1 To RowsCount
        If (ActiveCell.Value = ActiveSheet.Cells(ActiveCell.Row + 1, ActiveCell.Column).Value) And _
            ActiveSheet.Cells(ActiveCell.Row, colPO.Column).Value = ActiveSheet.Cells(ActiveCell.Row+1, colPO.Column).Value Then

                ActiveCell.EntireRow.Font.ColorIndex = Color
                ActiveCell.EntireRow+1.Font.ColorIndex = Color

        Else
            Color = Color + 5
        End If

        ActiveCell.Offset(1, 0).Select
    Next

End Sub

我在代码的以下几行中收到错误“下标超出范围”，并且不确定如何解决？

           ActiveCell.EntireRow.Font.ColorIndex = Color
           ActiveCell.EntireRow+1.Font.ColorIndex = Color

Answer 1

这主要是语法错误。 ActiveCell.EntireRow + 1.Font.ColorIndex = Color对Excel没有意义。导致该问题的是EntireRow + 1。您将不得不使用offset，就像我在下面的代码段中看到的那样。

尝试这个:

            ActiveCell.EntireRow.Font.ColorIndex = Color
            ActiveCell.offset(1,0).Select
            ActiveCell.EntireRow.Font.ColorIndex = Color

就像@brucewayne在他的评论中说的那样，您确实要尽可能避免选择/激活单元格。它会使您的代码变慢，并且可能很笨拙。

这是一篇有关如何以及为什么避免使用选择和激活 https://www.excelcampus.com/vba/how-to-avoid-the-select-method/的文章

for循环的强大功能之一是，您可以在代码中使用计数器变量(在您的情况下为“x”)代替偏移量。我已经使用两种不同的方法选择并激活了您的代码。请注意，我如何在循环中使用 cells(x,col)每次迭代向下移动一个单元格，以及如何使用 cells(x + 1,col)使其比x的值低一个。 (在测试代码之前，请务必将“工作表名称”替换为工作表的实际名称)

Sub changeTextColor()

Dim Color As Integer
Dim colReq As Range
Dim colPO As Range

Color = 5

    'Get number of rows in the specified column
RowsCount = Range("A1", Range("A1").End(xlDown)).Rows.Count



With Worksheets("Sheet Name").UsedRange.Rows(1)
    Set colReq = .Find(What:="Requisition Number", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    Set colPO = .Find(What:="PO #", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
End With



    'Loop the cells
    For x = 1 To RowsCount
        If (Worksheets("Sheet Name").Cells(x, colReq.Column).Value = Worksheets("Sheet Name").Cells(x + 1, colReq.Column).Value) And _
            Worksheets("Sheet Name").Cells(x, colPO.Column).Value = Worksheets("Sheet Name").Cells(x + 1, colPO.Column).Value Then

                Worksheets("Sheet Name").Cells(x, colReq.Column).EntireRow.Font.ColorIndex = Color
                Worksheets("Sheet Name").Cells(x + 1, colReq.Column).EntireRow.Font.ColorIndex = Color
        Else
            Color = Color + 5
        End If
    Next

End Sub