03_筛选标记2.0版和3.0版FIND及ColorIndex

2.0版

工作簿筛选标记

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

    Sheet1.Select

End 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:=Town
    End If


End Sub

sheet标记

'*********************************************************'
Sub 标记(wsh As Worksheet, Town As String)
        ' wsh.Activate
        Dim a
        Set a = Cells.Find(What:=Town)
        If Not a Is Nothing Then
            wsh.Tab.ColorIndex = 6
        Else
            Debug.Print (ActiveWorkbook.Name & ";" & wsh.Name & ";找不到")
        End If
End Sub

取消筛选标记

Sub 初始化() '取消筛选标记
    Dim wsh As Worksheet
    
    For Each wsh In Sheets
        wsh.Tab.ColorIndex = -4142 '取消颜色标记,取消筛选，取消隐藏
        wsh.AutoFilterMode = False
        Cells.EntireRow.Hidden = False
        Cells.EntireColumn.Hidden = False
    Next wsh

End Sub

3.0版

汇总:

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

    Sheet1.Select

End 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 If

End Sub
Sub 筛选封装(wsh As Worksheet, rng As String, fld As Byte, Town As String)
    wsh.Range(rng).AutoFilter field:=fld, Criteria1:=Town
End Sub

Sub 标记(wsh As Worksheet, Town)
        ' wsh.Activate
        Dim a
        Set a = Cells.Find(What:=Town)
        If Not a Is Nothing Then
            wsh.Tab.ColorIndex = 6
        Else
            Debug.Print (ActiveWorkbook.Name & ";" & wsh.Name & ";找不到")
        End If
End Sub

Sub 初始化() '取消筛选标记
    Dim wsh As Worksheet
    
    For Each wsh In Sheets
        wsh.Tab.ColorIndex = -4142 '取消颜色标记,取消筛选，取消隐藏
        wsh.AutoFilterMode = False
        Cells.EntireRow.Hidden = False
        Cells.EntireColumn.Hidden = False
    Next wsh

End Sub

ColorIndex 下标代码

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

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