比较ells A3和A2，如果相等，则没有其他颜色的第3行单元格A到F.重复下一行 [英] Compare ells A3 and A2, if equal, nothing else color row 3 cells A though F. Repeat with next row

问题描述

我所拥有的是标题行下面的合同列表。一些合同占用多行。

What I have are a list of contracts below a header row. Some contracts take up multiple rows.

我想要的是一个将单元格A3与A2进行比较的VBA宏。如果它们是相同的;下一个。如果它们不同，则选择单元格A3：F3并将Interior.Color更改为灰色。

What I would like is to have a VBA Macro that will compare cell A3 to A2. If they are the same; next. If they are different, then select the cells A3:F3 and change Interior.Color to grey.

然后将A4与A3进行比较，然后将A5与A4进行比较并重复所有使用的A列中的单元格，有效地制作表格。

Then compare A4 to A3, then A5 to A4 and repeat for all used cells in the A column, effectively making a table.

这就是屏幕的样子：

Row   Column A   Column B     C         D        E        F

1.    000000      (Info)   (Info)    (Info)   (Info)   (Info)
2.    111111      (Info)   (Info)    (Info)   (Info)   (Info)
3.    123456      (Info)   (Info)    (Info)   (Info)   (Info)
4.    123456      (Info)   (Info)    (Info)   (Info)   (Info)
5.    654321      (Info)   (Info)    (Info)   (Info)   (Info)
6.    124536      (Info)   (Info)    (Info)   (Info)   (Info)
7.    666666      (Info)   (Info)    (Info)   (Info)   (Info)

我希望看到的是：

1.    000000      (Info)   (Info)    (Info)   (Info)   (Info) 'line is clear
2.    111111      (Info)   (Info)    (Info)   (Info)   (Info) 'line is grey
3.    123456      (Info)   (Info)    (Info)   (Info)   (Info) 'line is clear
4.    123456      (Info)   (Info)    (Info)   (Info)   (Info) 'line is clear
5.    654321      (Info)   (Info)    (Info)   (Info)   (Info) 'line is grey
6.    124536      (Info)   (Info)    (Info)   (Info)   (Info) 'line is clear
7.    666666      (Info)   (Info)    (Info)   (Info)   (Info) 'line is grey

我已经花了一天时间搜索并找到了（并且使用了以下脚本，但它只是着色行中的第一个单元格。

I have spent my day searching and have found (and worked on the following script however it is only coloring the first cell in the line.

Sub Line_Shading()

    Application.ScreenUpdating = False
    Dim this As Variant
    Dim previous As Variant
    Dim currentColor As Long

    Dim rng As Range 
    Dim a As Range   
    ' pick a color to start with
    currentColor = 14277081              ' 14277081 Grey or 16777215 Clear
    ' rng = used and visible cells

    Set rng = Range("A2:A" & Range("A2").End(xlDown).Row)

    For Each a In rng
        If Not a.Row = 1 Then            ' skip header row
            this = a.Value
            'some simple test logic to switch colors
            If this <> previous Then
                If currentColor = 14277081 Then
                    currentColor = 16777215
                ElseIf currentColor = 16777215 Then
                    currentColor = 14277081
                End If
            End If

            'set interior color
            a.Interior.color = currentColor 'Interior.Color

            previous = this

       End If
    Next a
    Application.ScreenUpdating = True
End Sub

我觉得它只会是行的修改：
a.Interior.color = currentColor'Interior.Color
但是我看不到解决方案。

I feel that it will just be a modification of the line: a.Interior.color = currentColor 'Interior.Color but I just can't see the solution.

建议？

推荐答案

如果您有兴趣，可以使用以下公式进行条件格式化：

if you are interested, this can be done with conditional formatting with the following formula:

=ISEVEN(SUMPRODUCT(1/COUNTIFS($A$1:$A1,$A$1:$A1)))

如果你真的想使用vba，那么改变这一行：

If you really want to use vba then change the this line:

a.Interior.color = currentColor 'Interior.Color

to：

Range(Cells(a.Row, 1), Cells(a.Row, 6)).Interior.Color = currentColor 'Interior.Color

因此它将应用于所需范围内的整行而不仅仅是A列。

so it will apply to the entire row in the desired range and not just Column A.

这篇关于比较ells A3和A2，如果相等，则没有其他颜色的第3行单元格A到F.重复下一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！