根据内容自动设定颜色的EXCEL VBA脚本

帮同事做的一个简单宏，第一次使用VBA，记录下，以后再做可以参考

 

是根据第二页用户维护的数据，自动更新第一页的对应显示区的颜色，方便查看

 

这个是手动运行的，用来处理已有数据

Sub ChangeColumnsRGB()
        Dim i As Integer
        Dim row As Integer
        Dim column As Integer

    For i = 3 To Sheet2.UsedRange.Rows.Count
   
        finalResult = Sheet2.Cells(i, 11).Value
        row = (i - 3) \ 4 + 2
        column = (i - 3) Mod 4 + 1 + 2  

        If finalResult = "ok" Then
            Sheet1.Cells(row, column).Interior.color = RGB(51, 153, 102)
        End If

        If finalResult = "Need artwork" Then
            Sheet1.Cells(row, column).Interior.color = RGB(153, 204, 0)
        End If

        If finalResult = "test ongoing" Then
            Sheet1.Cells(row, column).Interior.color = RGB(153, 51, 0)
        End If

        If finalResult = "Samples ongoing" Then
            Sheet1.Cells(row, column).Interior.color = RGB(0, 128, 0)
        End If

    Next i
End Sub

 

这个是由用户输入操作触发的，自动处理

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.column = 11 Then
    
        Dim operator As String
        Dim color As String
        Dim partName As String
        Dim finalResult As String
        
        operator = Cells(Target.row, 1).Value
        color = Cells(Target.row, 2).Value
        partName = Cells(Target.row, 3).Value
        finalResult = Target.Value
        
        Dim row As Integer
        Dim column As Integer
        row = (Target.row - 3) \ 4 + 2
        column = (Target.row - 3) Mod 4 + 1 + 2
        
        If finalResult = "ok" Then
            Sheet1.Cells(row, column).Interior.color = RGB(51, 153, 102)
        End If
        If finalResult = "Need artwork" Then
            Sheet1.Cells(row, column).Interior.color = RGB(153, 204, 0)
        End If
        If finalResult = "test ongoing" Then
            Sheet1.Cells(row, column).Interior.color = RGB(153, 51, 0)
        End If
        If finalResult = "Samples ongoing" Then
            Sheet1.Cells(row, column).Interior.color = RGB(0, 128, 0)
        End If
        
    End If
    
End Sub