VBA code to change row color when value in column A changes

My spreadsheet is set up like this:

我的电子表格设置如下:

I have a list of job numbers in column A and a corresponding list of Job titles in Column B. I need for all of the rows of a certain job number to be colored. e.g., rows 2-4 have "3705" in column A, so those rows would be colored red. Rows 5 & 6 have 4169 in column A and would be colored green. Rows 7-10 have 5518 in column A and would be colored red (alternate between red and green).

我在A列中有一个作业号列表,在B列中有一个相应的作业列表。我需要为某个作业号的所有行着色。例如,行2-4在列A中具有“3705”,因此这些行将被涂成红色。第5行和第6行在A列中有4169,并且将显示为绿色。第7-10行在A列中有5518,颜色为红色(在红色和绿色之间交替)。

Can someone give me a VBA code that will do what I described above automatically/using a macro?

有人可以给我一个VBA代码,它会自动执行上面描述的/使用宏吗?

Thanks!!

2 个解决方案

#1

Sub colorize()

Dim r As Long, val As Long, c As Long

    r = 1
    val = ActiveSheet.Cells(r, 1).Value
    c = 4 '4 is green, 3 is red '

    For r = 1 To ActiveSheet.Rows.Count
        If IsEmpty(ActiveSheet.Cells(r, 1).Value) Then
            Exit For
        End If

        If ActiveSheet.Cells(r, 1).Value <> val Then
            If c = 3 Then
                c = 4
            Else
                c = 3
            End If
        End If

        ActiveSheet.Rows(r).Select
        With Selection.Interior
            .ColorIndex = c
            .Pattern = xlSolid
        End With

        val = ActiveSheet.Cells(r, 1).Value
    Next

End Sub

Add the following to the relevant Worksheet's module if you want the macro to fire every time a cell in column A changes value:

如果希望每次A列中的单元格更改值时都触发宏,请将以下内容添加到相关的工作表模块中:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("A:A")) Is Nothing Then
        Exit Sub
    End If

    colorize

End Sub

#2

This can be done using only Excel formulas and conditional formatting if you can add a column. You might not be able to alternate red/green, but only a color and no color.

如果您可以添加列,则只能使用Excel公式和条件格式来完成此操作。您可能无法交替红色/绿色,但只能使用颜色而不是颜色。

Alternate Excel Row Color Based on Content

基于内容的备用Excel行颜色

#1

Sub colorize()

Dim r As Long, val As Long, c As Long

    r = 1
    val = ActiveSheet.Cells(r, 1).Value
    c = 4 '4 is green, 3 is red '

    For r = 1 To ActiveSheet.Rows.Count
        If IsEmpty(ActiveSheet.Cells(r, 1).Value) Then
            Exit For
        End If

        If ActiveSheet.Cells(r, 1).Value <> val Then
            If c = 3 Then
                c = 4
            Else
                c = 3
            End If
        End If

        ActiveSheet.Rows(r).Select
        With Selection.Interior
            .ColorIndex = c
            .Pattern = xlSolid
        End With

        val = ActiveSheet.Cells(r, 1).Value
    Next

End Sub