1Excel筛选后如何快速用颜色分隔数字区间标示
工具/原料
- Microsoft Office Excel 2007
- Excel VBA
处置方式/步骤
- 1
首先打开Microsoft Office Excel 2007，新建文档并保留文件名《Excel如何建立一、二、三级联动下拉菜单 .xlsm》（演示文件，下面代码复制到能运行宏的工作簿都可以）如图。
- 2
在“Sheet2”表建立数据如下。
- 3
鼠标移到当前工作表标签栏“Sheet1”表（需要在哪表计较在哪表），右键，弹出快捷菜单，如下图。
- 4
在快捷菜单找到【查看代码】并单击，打开VBE（宏）编纂界面，如下图。
- 5
在右边代码框中复制下面代码到该框中，如下图：
Private Sub Worksheet_Change(ByVal Target As Range)
'2020-10-8 22:38:36
If Target.Column = 1 Then
Target(1).Offset(0, 1).Resize(1, 2).ClearContents
End If
If Target.Column = 2 Then
Target(1).Offset(0, 1).ClearContents
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'2020-10-8 22:45:12
Dim i As Integer, mb(), m As String, d As Object, c As Long
If Target.Column > 3 Then Exit Sub
On Error Resume Next
Set d = CreateObject("Scripting.Dictionary")
With d
mb = Worksheets("Sheet2").Range("A2").Resize(Worksheets("Sheet2").UsedRange.Rows.Count - 1, 3).Value
c = Target.Column
If c = 1 Then
For i = 1 To UBound(mb, 1)
.Add CStr(mb(i, 1)),""
Next
m = Join(.keys,",")
With Target(1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=m
End With
ElseIf c = 2 Then
For i = 1 To UBound(mb, 1)
If Target(1).Offset(0, -1) = mb(i, 1) Then
.Add CStr(mb(i, 2)),""
End If
Next
m = Join(.keys,",")
With Target(1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=m
End With
ElseIf c = 3 Then
For i = 1 To UBound(mb, 1)
If Target(1).Offset(0, -2) = mb(i, 1) And Target(1).Offset(0, -1) = mb(i, 2) Then
.Add CStr(mb(i, 3)),""
End If
Next
m = Join(.keys,",")
With Target(1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=m
End With
End If
.RemoveAll
End With
End Sub
- 6
以上操作动态过程如下：
- 7
回到工作表窗口，“Sheet1”表建立了三级菜单了，是很便利利用，运行过程如下图。
- 8
若是感觉这篇经验帮到了您，请点击下方的 “投票点赞" 或者“保藏”撑持我！还有疑问的话可以点击下方的 “我有疑问”，感谢啦！
注重事项
- 动态图片要双击当作零丁大图才能播放全过程。
