根据下拉值替换颜色

Question

我一直在研究一个宏，它根据下拉值填充某些带有颜色的单元格。当选择一个值然后更改它时，它不会删除上次基于该值填充的先前颜色。当工作表发生更改时，宏在私有子上运行。然后检查单元格值并使用颜色填充字段。当某些事情发生变化以重置活动行时，私有子是否值得使用?下面是在字段中着色的宏代码:

 Sub Validate()

Dim rng As Range
Dim row As Range
Dim cell As Range
Dim counter As Long
Dim clrGrren As Long
Dim clrWhite As Long
clrGreen = RGB(Red:=180, Green:=236, Blue:=180)
clrWhite = RGB(Red:=255, Green:=255, Blue:=255)
Set rng = Range("D4:D1000")

For Each cell In rng

Select Case cell.Value
Case Is = "Action Figures"
    cell.Offset(counter, 12).Interior.Color = clrGreen
    cell.Offset(counter, 13).Interior.Color = clrGreen
    cell.Offset(counter, 21).Interior.Color = clrGreen
    cell.Offset(counter, 22).Interior.Color = clrGreen
    cell.Offset(counter, 23).Interior.Color = clrGreen
    cell.Offset(counter, 29).Interior.ColorIndex = 16
    cell.Offset(counter, 30).Interior.ColorIndex = 16
    cell.Offset(counter, 31).Interior.Color = clrGreen
    cell.Offset(counter, 32).Interior.Color = clrGreen
    cell.Offset(counter, 34).Interior.ColorIndex = 16
    cell.Offset(counter, 35).Interior.Color = clrGreen
    cell.Offset(counter, 38).Interior.ColorIndex = 16
    cell.Offset(counter, 39).Interior.ColorIndex = 16
    cell.Offset(counter, 41).Interior.ColorIndex = 16
    cell.Offset(counter, 42).Interior.ColorIndex = 16
    cell.Offset(counter, 43).Interior.ColorIndex = 16
    cell.Offset(counter, 44).Interior.ColorIndex = 16

Answer 1

您应该包含检查单元格值并在Private Sub Worksheet_Change()中更新相应的单元格颜色的代码，而不是用户定义的sub。保存工作表，然后尝试更新值。目标单元格的颜色将自动改变。

Dim rng As Range
Dim row As Range
Dim cell As Range
Dim counter As Long
Dim clrGreen As Long
Dim clrWhite As Long
Dim clrBlue As Long
    Private Sub Worksheet_Change(ByVal cell As Range)
    clrGreen = RGB(Red:=180, Green:=236, Blue:=180)
    clrWhite = RGB(Red:=255, Green:=255, Blue:=255)
    clrBlue = RGB(Red:=0, Green:=0, Blue:=255)
    Select Case cell.Value
    Case Is = "Action Figures"
        cell.Offset(counter, 12).Interior.Color = clrGreen
        cell.Offset(counter, 13).Interior.Color = clrGreen
        cell.Offset(counter, 21).Interior.Color = clrGreen
        cell.Offset(counter, 22).Interior.Color = clrGreen
        cell.Offset(counter, 23).Interior.Color = clrGreen
        cell.Offset(counter, 29).Interior.ColorIndex = 16
        cell.Offset(counter, 30).Interior.ColorIndex = 16
        cell.Offset(counter, 31).Interior.Color = clrGreen
        cell.Offset(counter, 32).Interior.Color = clrGreen
        cell.Offset(counter, 34).Interior.ColorIndex = 16
        cell.Offset(counter, 35).Interior.Color = clrGreen
        cell.Offset(counter, 38).Interior.ColorIndex = 16
        cell.Offset(counter, 39).Interior.ColorIndex = 16
        cell.Offset(counter, 41).Interior.ColorIndex = 16
        cell.Offset(counter, 42).Interior.ColorIndex = 16
        cell.Offset(counter, 43).Interior.ColorIndex = 16
        cell.Offset(counter, 44).Interior.ColorIndex = 16
    Case Is = "Dolls"
        cell.Offset(counter, 12).Interior.Color = clrBlue
        cell.Offset(counter, 13).Interior.Color = clrBlue
        cell.Offset(counter, 21).Interior.Color = clrBlue
        cell.Offset(counter, 22).Interior.Color = clrBlue
        cell.Offset(counter, 23).Interior.Color = clrBlue
        cell.Offset(counter, 29).Interior.ColorIndex = 16
        cell.Offset(counter, 30).Interior.ColorIndex = 16
        cell.Offset(counter, 31).Interior.Color = clrBlue
        cell.Offset(counter, 32).Interior.Color = clrBlue
        cell.Offset(counter, 34).Interior.ColorIndex = 16
        cell.Offset(counter, 35).Interior.Color = clrBlue
        cell.Offset(counter, 38).Interior.ColorIndex = 16
        cell.Offset(counter, 39).Interior.ColorIndex = 16
        cell.Offset(counter, 41).Interior.ColorIndex = 16
        cell.Offset(counter, 42).Interior.ColorIndex = 16
        cell.Offset(counter, 43).Interior.ColorIndex = 16
        cell.Offset(counter, 44).Interior.ColorIndex = 16
    End Select
End Sub