VBA 对具有不同范围和工作表的单元格颜色进行排序

VBA sorting cell color with varying ranges and sheets

提问by Danny M

I am currently learning/messing around with VBA coding so I could write some macros for work. I could really use some help please.

我目前正在学习/弄乱 VBA 编码，以便我可以编写一些宏来工作。我真的可以请一些帮助。

Question: I want the code below (which was written by macros recorder) to add some specific codes for my needs.

问题：我希望下面的代码（由宏记录器编写）添加一些满足我需要的特定代码。

Sub Sort()

Range("A1:D23").Select
ActiveWindow.SmallScroll Down:=-15
ActiveWorkbook.Worksheets("Sheet8").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet8").Sort.SortFields.Add(Range("A2:A23"), _
    xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(255, _
    255, 0)
With ActiveWorkbook.Worksheets("Sheet8").Sort
    .SetRange Range("A1:D23")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply

End Sub

结束子

1. The first line I would like it not to be static with specific range. Would substituting Range("A1:D23").Select for Range("A1").CurrentRegion.Select work for the varying ranges in my data tables?

2. For lines 3, 4 and 5 where it says ActiveWorkbook.Worksheets("Sheet8") how can I make it so that its active not only for sheet8 but for any sheet I open or create?

1. 第一行我希望它不是具有特定范围的静态。替换 Range("A1:D23").Select for Range("A1").CurrentRegion.Select 是否适用于我的数据表中的不同范围？

2. 对于第 3、4 和 5 行，它说 ActiveWorkbook.Worksheets("Sheet8") 我怎样才能使它不仅对 sheet8 而且对我打开或创建的任何工作表都有效？

3.Finally on line 4 Sort.SortFields.Add(Range("A2:A23"), I would like to change to the varying row amounts under that column from varying data

3.最后在第 4 行 Sort.SortFields.Add(Range("A2:A23")，我想从不同的数据更改为该列下的不同行数

I hope I was clear enough. Please help if you can

我希望我说得够清楚了。如果可以的话请帮忙

回答by Paulo Avelar

This might help:

这可能有帮助：

1. Get how many rows your sheet has and work with it. See below for more information. Beware: it may be glitchy if the sheet is empty.

2. Replace ActiveWorkbook.Sheets("Sheet 8")with ActiveSheet. Unfortunately, ActiveSheet does not provide autocomplete, but it is a Sheet object. Casting it to any Sheet object would give you that behavior.

3. Well, once you want your code to deal with the same range twice, you might as well save it in a variable.

1. 获取您的工作表有多少行并使用它。请参阅下面的详细信息。当心：如果工作表是空的，它可能会出现故障。

2. 替换ActiveWorkbook.Sheets("Sheet 8")为ActiveSheet。不幸的是，ActiveSheet 不提供自动完成功能，但它是一个 Sheet 对象。将它投射到任何 Sheet 对象都会给你这种行为。

3. 好吧，一旦您希望您的代码处理相同的范围两次，您不妨将其保存在一个变量中。

Considering your table may expand in rows and columns, you need to count them using the Endcommand.

考虑到您的表格可能会在行和列中扩展，您需要使用该End命令对其进行计数。

Here's the updated code:

这是更新后的代码：

Sub Sort()
    Dim sht As Worksheet
    Dim rngSort As Range
    Dim rngTable As Range
    Set sht = ActiveSheet        

    rowCount = sht.Range("A1").End(xlDown).Row
    Set rngSort = sht.Range("A1:A" & rowCount)
    Set rngTable = sht.Range(sht.Cells(1, 1), sht.Cells(rowCount, 1).End(xlToRight))

    sht.Sort.SortFields.Clear
    sht.Sort.SortFields.Add(rngSort, _
        xlSortOnCellColor, xlDescending, , _
        xlSortNormal).SortOnValue.Color = RGB(255, 255, 0)

    With sht.Sort
        .SetRange rngTable
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Take a look at my range. I use sht.Rangebecause when you don't say where your range is, Excel assumes it is your selected sheet. If it isn't you must explicitly say so. In your case, it doesn't matter.

看看我的范围。我使用sht.Range是因为当您没有说明您的范围在哪里时，Excel 会假定它是您选择的工作表。如果不是，您必须明确说明。在你的情况下，没关系。