文章目录

• • • 2.0版
    • 工作簿筛选标记
    • • 筛选
      • sheet标记
      • 取消筛选标记
    • 3.0版
    • • ColorIndex 下标代码
      • • 特别鸣谢,大佬的分享FIND方法的使用

2.0版

工作簿筛选标记

Option Explicit
Sub 自动筛选()Dim Town As StringDim wsh As WorksheetCall 初始化  ' 初始化表格状态Town = InputBox("请输入街道名称!")  '街道输入For Each wsh In Sheets     '表格循环wsh.SelectCall 筛选(wsh, Town)Call 标记(wsh, Town)Next wshSheet1.SelectEnd Sub

筛选

'*********************************************************'
Sub 筛选(wsh As Worksheet, Town As String)If wsh.Range("G1").Value <> "乡（镇、街道）" Then         'G列标题判定wsh.Range("I2").AutoFilter Field:=9, Criteria1:=Town 'I列标题判定Else:wsh.Range("G1").AutoFilter Field:=7, Criteria1:=TownEnd IfEnd Sub

sheet标记

'*********************************************************'
Sub 标记(wsh As Worksheet, Town As String)' wsh.ActivateDim aSet a = Cells.Find(What:=Town)If Not a Is Nothing Thenwsh.Tab.ColorIndex = 6ElseDebug.Print (ActiveWorkbook.Name & ";" & wsh.Name & ";找不到")End If
End Sub

取消筛选标记

Sub 初始化() '取消筛选标记Dim wsh As WorksheetFor Each wsh In Sheetswsh.Tab.ColorIndex = -4142 '取消颜色标记,取消筛选，取消隐藏wsh.AutoFilterMode = FalseCells.EntireRow.Hidden = FalseCells.EntireColumn.Hidden = FalseNext wshEnd Sub

3.0版

汇总:

Option Explicit
Sub 自动筛选()Dim Town As StringDim wsh As WorksheetCall 初始化  ' 初始化表格状态Town = InputBox("请输入街道名称!")  '街道输入For Each wsh In Sheets     '表格循环wsh.SelectCall 筛选(wsh, Town)Call 标记(wsh, Town)Next wshSheet1.SelectEnd Sub
Sub 筛选(wsh As Worksheet, Town As String)If wsh.Range("G1").Value = "乡（镇、街道）" Then         'G列标题判定Call 筛选封装(wsh, "G1", 7, Town)ElseIf wsh.Range("F2").Value = "乡" Then     'F列标题判定-->相邻月和单月Call 筛选封装(wsh, "F2", 6, Town)Else:                                                       'I列标题判定Call 筛选封装(wsh, "I2", 9, Town)End IfEnd Sub
Sub 筛选封装(wsh As Worksheet, rng As String, fld As Byte, Town As String)wsh.Range(rng).AutoFilter field:=fld, Criteria1:=Town
End SubSub 标记(wsh As Worksheet, Town)' wsh.ActivateDim aSet a = Cells.Find(What:=Town)If Not a Is Nothing Thenwsh.Tab.ColorIndex = 6ElseDebug.Print (ActiveWorkbook.Name & ";" & wsh.Name & ";找不到")End If
End SubSub 初始化() '取消筛选标记Dim wsh As WorksheetFor Each wsh In Sheetswsh.Tab.ColorIndex = -4142 '取消颜色标记,取消筛选，取消隐藏wsh.AutoFilterMode = FalseCells.EntireRow.Hidden = FalseCells.EntireColumn.Hidden = FalseNext wshEnd Sub

ColorIndex 下标代码

特别鸣谢,大佬的分享FIND方法的使用

链接: VBA小程序–Find返回值（以及不适用On Error GOTO 方式处理的异常方式）遍历所有工作表 + 遍历文件夹内所有文件 查找是否有所需要搜索/检索的关键词