vba 按字母顺序和单元格颜色排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17530785/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sorting Alphabetically and by Cell Color
提问by Batman
I'm trying to sort multiple lists Alphabetically and by Cell Color but within a certain range (so it doesn't take too long). Basically the VBA is suppose to select for example column B rows 3-88, sort by alphabet, then sort by color. Then move to next column C3:C88, etc, until column NY.
我正在尝试按字母顺序和单元格颜色对多个列表进行排序，但在一定范围内（因此不会花费太长时间）。基本上，VBA 假设选择例如 B 列第 3-88 行，按字母排序，然后按颜色排序。然后移动到下一列 C3:C88 等，直到列 NY。
When I try it, I get Run-time error 1004: Mathod "Range" of object '_Global' failed.
当我尝试时，我收到运行时错误 1004：对象“_Global”的 Mathod“Range”失败。
This is my VBA:
这是我的 VBA：
Sub SortAlphaColor()
' Sorts rows within a list from A-Z
' Run Clean all first to avoid sorting blanks
' Set maximum range to avoid sorting too many rows
Dim rngFirstRow As Range
Dim rng As Range
Dim ws As Worksheet
Application.ScreenUpdating = False
Set ws = ActiveSheet
Set rngFirstRow = ws.Range("B3:NY3")
For Each rng In rngFirstRow
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=rng, Order:=xlAscending
'assuming there are no blank cells..
.SetRange ws.Range(rng, rng.Range("B88").End(xlUp))
'VBA from second module
.SortFields.Add(Range(rng), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(198, 239, 206) <- this line is highlighted in debug
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Header = xlYes
.MatchCase = False
.Apply
End With
Next rng
Application.ScreenUpdating = True
End Sub
采纳答案by Tim Williams
This worked for me:
这对我有用：
Sub SortAlphaColor()
Dim rngFirstRow As Range
Dim rng As Range, rngSort As Range
Dim ws As Worksheet
Application.ScreenUpdating = False
Set ws = ActiveSheet
Set rngFirstRow = ws.Range("B3:NY3")
For Each rng In rngFirstRow.Cells
With ws.Sort
Set rngSort = rng.Resize(86, 1) 'to row 88
.SortFields.Clear
.SortFields.Add Key:=rng, SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add(rng, xlSortOnCellColor, xlAscending, , xlSortNormal). _
SortOnValue.Color = RGB(198, 239, 206)
.SetRange rngSort
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next rng
Application.ScreenUpdating = True
End Sub
