02_工作簿筛选标记1.0

pigerr杨

已于 2023-01-21 23:52:40 修改

阅读量58

点赞数

分类专栏： # VBA业务收集篇文章标签： VBA

于 2023-01-20 21:08:45 首次发布

本文链接：https://blog.csdn.net/pigerr/article/details/128743769

版权

VBA业务收集篇专栏收录该内容

9 篇文章 0 订阅

订阅专栏

知识点:

方法调用,传参无返回值
for each …next循环
AutoFilter 筛选属性
StrComp(str1,str2,0/-1) 字符串比较
ActiveSheet.Tab.ColorIndex 工作表标色
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