Excel 2010 VBA交替行颜色，从A5开始变化的范围 [英] Excel 2010 VBA alternate row color on changing range starting with A5

问题描述

在我的Excel工作表中，我需要在特定的范围上应用备用颜色，这些颜色将始终以A5开头，并以X列结尾，但每次运行报表时，行数都会更改。

在第一个范围结束时，我需要向下移动2行，并将替代行颜色应用到4行。

我发现以下代码在stackoverflow，但到目前为止，我只能得到第A5行突出显示。

  Sub AlternateRowColors（）
 Dim LastRow As Long 
 
'LastRow = Range（ A1）。End（xlDown）'Row从原始代码
 
'查找列中的最后一行：列A在此示例中
'Dim LastRow As Long 
 With ActiveSheet 
 LastRow = .Cells（.Rows.Count，A）。End（xlUp）.Row 
 
 End with 
 
对于范围内的每个单元格A5：x& LastRow）''相应地改变范围
如果Cell.Row Mod 2 = 1然后''突出显示行2,4,6等| = 0亮点1,3,5 
 Cell.Interior.ColorIndex = 15颜色到首选项
 Else 
 Cell.Interior.ColorIndex = xlNone''要偏好或删除
结束如果
下一个单元格
 
 End Sub 
  

我一直在试图弄清楚这两天，任何帮助将不胜感激。

解决方案

我不知道我完全明白你的问题m，但是它的声音好像是与不正确的LastRow有关。改用这种方法。我已经评论过您当前的LastRow方法，并输入了一个新的。如果您希望表格中绝对的最后一行，无论哪列具有最后一个数据，那么这将帮助您：

  Sub AlternateRowColors（）
 Dim LastRow As Long 
 
 With ActiveSheet 
'LastRow = .Cells（.Rows.Count，A）。End（xlUp）.Row 
 LastRow = Cells.Find（*，SearchOrder = = xlByRows，SearchDirection：= xlPrevious）.Row 
结束
 
对于范围内的每个单元格（A5：x & LastRow）'相应地改变范围
如果Cell.Row Mod 2 = 1然后''突出显示行2,4,6 etc | = 0亮点1,3,5 
 Cell.Interior。 ColorIndex = 15颜色到偏好
 Else 
 Cell.Interior.ColorIndex = xlNone''要偏好或删除
结束如果
下一个单元格
 End Sub 
  

---

其他编辑：您询问有关停止高亮8行在LastRow之前。建立之后，我们可以改变LastRow的价值。只需在代码的中间添加以下两行（在End With之后，在每个循环之前）：

  LastRow = LastRow  -  8 
如果LastRow< 5 Then LastRow = 5 
  

第一行更改了LastRow变量;第二行检查LastRow值是否现在小于我们预期的起始行（第5行）。如果它较少，则将其更改为5，因为5是最小值（从第5行开始，在第5行或更高版本结束）。

---

其他编辑2：你说LastRow下面的行是一个合并的行，不会被突出显示，但接下来的4行将具有不同的高亮颜色...尝试下面的代码。主要的补充是最后一行代码，这是单一范围的颜色，你描述的四行。不过要使用这行代码，我不得不引用真正的LastRow，所以我不得不改变脚本的中间部分 - 它现在创建一个新的'LastRowNew'，以便像以前一样为替代行着色。 / p>

希望这是你现在需要的;但是如果有什么不完全符合您的需要，您应该可以根据您的要求进行调整。希望这有帮助！

  Sub AlternateRowColors（）
 Dim LastRow As Long 
 
 With ActiveSheet 
'LastRow = .Cells（.Rows.Count，A）。End（xlUp）.Row 
 LastRow = Cells.Find（*，SearchOrder = = xlByRows，SearchDirection = = xlPrevious） .Row 
结束
 
 LastRowNew = LastRow  -  8 
如果LastRowNew< 5 Then LastRowNew = 5 
 
对于范围内的每个单元格（A5：x& LastRowNew）更改范围相应
如果Cell.Row Mod 2 = 1 Then''highlight row 2,4,6等| = 0亮点1,3,5 
 Cell.Interior.ColorIndex = 15'颜色到偏好
 Else 
 Cell.Interior.ColorIndex = xlNone''color （b）b 
 $ b范围（A&（LastRow + 2），范围（X&（LastRow + 5）） ）.Interior.ColorIndex = 45 
 
 End Sub 
  

In my Excel worksheet I need to apply alternate colors on a specific range that will always start in A5 and end in column X, however the number of lines will change each time the report is ran.

At the end of the first range I need to move down 2 rows and apply alternate row color to the 4 rows.

I found the following code on stackoverflow but so far I can only get row A5 to highlight.

Sub AlternateRowColors()
    Dim LastRow As Long

        'LastRow = Range("A1").End(xlDown) 'Row From original code

    'Find the last used row in a Column: column A in this example
    'Dim LastRow As Long
    With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

End With

For Each Cell In Range("A5:x" & LastRow) ''change range accordingly
    If Cell.Row Mod 2 = 1 Then ''highlights row 2,4,6 etc|= 0 highlights 1,3,5
        Cell.Interior.ColorIndex = 15 ''color to preference
    Else
        Cell.Interior.ColorIndex = xlNone ''color to preference or remove
    End If
Next Cell

End Sub

I have been trying to figure this out for two days now, any help will be greatly appreciated.

解决方案

I'm not sure I completely understand your problem, however it sounds as though it's to do with an inaccurate LastRow. Try this method instead. I've commented out your current LastRow method and entered a new one. If you want the absolute last row in your sheet, regardless of which column has the last item of data, then this will help you:

Sub AlternateRowColors()
    Dim LastRow As Long

    With ActiveSheet
        'LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With

For Each Cell In Range("A5:x" & LastRow) ''change range accordingly
    If Cell.Row Mod 2 = 1 Then ''highlights row 2,4,6 etc|= 0 highlights 1,3,5
        Cell.Interior.ColorIndex = 15 ''color to preference
    Else
        Cell.Interior.ColorIndex = xlNone ''color to preference or remove
    End If
Next Cell
End Sub

---

Additional edit: You asked about stopping the highlight 8 rows before the LastRow. We could change the value of the LastRow after establishing it. Simply add the following two lines in the middle of the code (after the End With, and before the For Each loop):

LastRow = LastRow - 8
If LastRow < 5 Then LastRow = 5

The first line changes the LastRow variable; the second line checks if the LastRow value is now less than our intended starting row (row 5). If it is less, then it changes it to 5, as 5 is the minimum (starts on row 5, ends on row 5 or later).

---

Additional edit 2: You say that the row below the "LastRow" is a merged row and would not be highlighted, but the next 4 rows will have a different highlight color... try out the code below. The main addition is the last line of code which colours the single range, the four lines as you describe. However to use this line of code I had to reference the genuine LastRow, and so I had to change the middle of the script - it now creates a new 'LastRowNew' for the purpose of colouring the alternate rows, as it did before.

Hopefully this is what you need now; but if anything isn't exactly how you need it, you should be able to tweak it to your requirements. Hope this helps!

Sub AlternateRowColors()
    Dim LastRow As Long

    With ActiveSheet
        'LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With

    LastRowNew = LastRow - 8
    If LastRowNew < 5 Then LastRowNew = 5

    For Each Cell In Range("A5:x" & LastRowNew) ''change range accordingly
        If Cell.Row Mod 2 = 1 Then ''highlights row 2,4,6 etc|= 0 highlights 1,3,5
            Cell.Interior.ColorIndex = 15 ''color to preference
        Else
            Cell.Interior.ColorIndex = xlNone ''color to preference or remove
        End If
    Next Cell

    Range("A" & (LastRow + 2), Range("X" & (LastRow + 5))).Interior.ColorIndex = 45

End Sub

这篇关于Excel 2010 VBA交替行颜色，从A5开始变化的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！