02_工作簿筛选标记1.0

知识点:

1. 方法调用,传参无返回值
2. for each …next循环
3. AutoFilter 筛选属性
4. StrComp(str1,str2,0/-1) 字符串比较
5. ActiveSheet.Tab.ColorIndex 工作表标色
6. AutoFilterMode 工作表筛选状态

Option Explicit
Sub 自动筛选()

Dim Town As String
Dim wsh As Worksheet
 
Call 取消筛选标记  ' 初始化表格状态
  
Town = InputBox("请输入街道名称!")  ' 交互式输入

For Each wsh In Sheets     '表格循环

    wsh.Select
    If wsh.Range("G1").Value = "乡（镇、街道）" Then    'G列标题判定
        wsh.Range("G1").AutoFilter Field:=7, Criteria1:=Town
       
    Else:                                               'I列标题判定
        wsh.Range("I2").AutoFilter Field:=9, Criteria1:=Town
       
        Call SheetColor(wsh, Town)  'sheet标签标色--->方法调用
    End If
Next wsh

Sheet1.Select

End Sub

Sub SheetColor(wsh As Worksheet, Town As String)     '---->挨个对比太繁琐,进阶为find查找高效无遗漏 详情对比03

    Dim rage As Range
    For Each rage In wsh.Range("G2:I500")
        If StrComp(rage.Value, Town) = 0 Then
           ActiveSheet.Tab.ColorIndex = 6
           Exit For
        End If
    Next rage
End Sub

Sub 取消筛选标记()
    Dim wsh As Worksheet
    For Each wsh In Sheets
        wsh.Tab.ColorIndex = -4142 '判断，并取消筛选状态
        wsh.AutoFilterMode = False '判断，并取消筛选状态
    Next wsh
End Sub