文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >基于颜色的行排序

问基于颜色的行排序
EN

Stack Overflow用户
提问于 2014-05-15 20:37:57
回答 1查看 1.2K关注 0票数 0

我在Excel中有条件地格式化了工作表，这样如果单元格包含相同的文本值，它们就被标记为相同的颜色。我想根据颜色自动对行进行排序。是否有一种方法可以自动地对行进行排序，而不必经过硬代码并定义行排序的顺序？

，这是我到目前为止所做的：是否有办法使其更具动态性？

代码语言：javascript
复制
   Rows("7:7").Select 'Indicates Header Row
   Range("B7").Activate 'Indicates Which Column to Sort Rows Based On
   Selection.AutoFilter
   ActiveSheet.AutoFilter.Sort.SortFields.Clear
   ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 1
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
       ActiveSheet.AutoFilter.Sort.SortFields.Clear
   ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 2
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
          ActiveSheet.AutoFilter.Sort.SortFields.Clear
   ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 3
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 4
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 5
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 6
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 7
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 8
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 9
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 10
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 11
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 12
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 13
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 14
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 15
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 16
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 17
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 18
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 19
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 20
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 21
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 22
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 23
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 24
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 25
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 26
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 27
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 28
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 29
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 30
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 31
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 32
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 33
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 34
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 35
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 36
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 37
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 38
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 39
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 41
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 42
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 43
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 44
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 45
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 46
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 47
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 48
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 49
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 50
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 51
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 52
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 53
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 54
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 55
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add(Range("B7") _
    , xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 56
With ActiveSheet.AutoFilter.Sort
    .header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub
excel
colors
vba
sorting
EN

回答 1

Stack Overflow用户

发布于 2014-05-16 04:07:38

假设Sort和Autofilter在这里符合您的规范，那么使用For循环似乎可以节省一些主要的重复：

代码语言：javascript
复制
Dim Index As Long
Dim MySheet As Worksheet

'set reference up-front
Set MySheet = ThisWorkbook.ActiveSheet

'...
'do whatever you need to for set up
'...

'set the 56 term however you see fit... find the last row maybe?
For Index = 1 To 56
    With MySheet.Sort.SortFields
        .Clear
        .Add(Range("B7"), xlSortOnCellColor, xlAscending, , _
            xlSortNormal).SortOnValue.ColorIndex = Index '<~ loop comes in handy
    End With
    With MySheet.AutoFilter.Sort
        .header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Next Index
'...
'do whatever you need to clean up and finish
'...
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/23688167

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档