目前,我在某些单元格上有一个下拉列表,与状态列表相连。“完成”标记为绿色,“待处理”标记为橙色,“未完成”标记为红色。我希望每个下拉选项也能触发单元格颜色变化。例如,如果单元格 H23 从下拉栏设置为“完成”,我希望单元格 H22 自动更改为“完成”。但是,如果单元格 H23 从其下拉菜单中设置为“完成”,而单元格“H24”设置为未完成,那么我希望它将单元格 H23 的颜色和状态自动更改为橙色。
答案1
正如其他海报所述,您的要求并不十分明确,但我添加了一个带有宏的示例文件,我认为它可以满足您的需求。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim PreviousRowStatus As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("H:H")
Application.EnableEvents = False
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
' Did a change occur to the status column (other than the column header?
If Range(Target.Address).Column = "8" And Range(Target.Address).Row <> 1 _
And Range(Target.Address).Row <> 2 Then ' Column H is column 8
' If the values are already the same, there's no need to make an update.
If Target.Offset(-1, 0).Value <> Target.Value Then
' Update the value of the previous row to match newly entered row
Target.Offset(-1, 0).Value = Target.Value
' Display a message when one of the designated cells has changed.
' MsgBox "Cell " & Target.Offset(-1, 0).Address & " has changed to " & _
Target.Value & " to match data in subsequent row."
End If
End If
End If
Application.EnableEvents = True
End Sub
使用条件格式自动设置颜色。如果您想了解更多有关其工作原理的信息,请参阅下面的链接。