下面是我想要澄清的代码:
'Auto format the cells when you change cell B39
If Not Intersect(Target, Range("B39")) Is Nothing Then
If InStr(1, Range("B39"), "ABC") > 0 Then
Range("B13:B18,B22,B23,B25").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = RGB(100, 250, 150)
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("B19:B21,B24,B26:B35").Select
With Selection.Interior
.Pattern = xlNone
.PatternTintAndShade = 0
End With
Else: Range("B13:B35").Select
With Selection.Interior
.Pattern = xlNone
.PatternTintAndShade = 0
End With
End If
End If
If Not Intersect(Target, Range("B57")) Is Nothing Then
If Range("B57") = "DEF" Then
Range("B13:B18,B22,B23,B25,B30,B35").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = RGB(100, 250, 150)
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("B19:B21,B24,B26,B27:B29,B31:B34").Select
With Selection.Interior
.Pattern = xlNone
.PatternTintAndShade = 0
End With
End If
End If
End Sub
在单元格B39和B57中,我有该公司生产的产品的下拉列表。上面的代码强调了制造这些产品的要求。当我从B39中选择一个SKU时,代码会突出显示指定的范围。B57也是如此。当我先更改B39,然后更改B57时,突出显示的单元格会发生变化,我不希望发生这种情况。我希望即使在更改B57之后,由于从B39中选择SKU而导致的更改也会保留。
希望这个澄清是更好的。
谢谢!
发布于 2018-10-16 03:25:13
看起来问题是你有重叠的范围,你想要/清除:当你有重叠材料的产品时,你不能像你试图处理的那样管理突出显示。您需要做的是清除all高亮显示,然后检查每个“目标”单元格以查看需要重新添加哪些高亮显示:不要只检查更改的一个目标单元格。
我移动了一个单独的子,并删除了任何选择步骤-这些通常是最好的避免。
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B39,B57")) Is Nothing Then
Hilite Range("B13:B100"), False '<< clear *all* hiliting
'add back any required hilites
If InStr(1, Range("B39"), "ABC") > 0 Then
Hilite Range("B13:B18,B22,B23,B25"), True
End If
If Range("B57") = "DEF" Then
Hilite Range("B13:B18,B22,B23,B25,B30,B35"), True
End If
End If
End Sub
'add/remove hilighting on a supplied range
Sub Hilite(rng As Range, hilight As Boolean)
With rng.Interior
If hilight Then
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = RGB(100, 250, 150)
.TintAndShade = 0
.PatternTintAndShade = 0
Else
.Pattern = xlNone
.PatternTintAndShade = 0
End If
End With
End Sub
https://stackoverflow.com/questions/52821026
复制相似问题