vba 对于每列突出显示最大值（Excel）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8397361/
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
For each Column highlight max value (Excel)
提问by Stefan
I have an Excel Sheet with values going in each column from cells 2:21
我有一个 Excel 工作表，每个列中的值都来自单元格 2:21
I need to highlight the corresponding cell in each column with the maximum value and try to loop through it with a macro. But I only know how to do it for a given hard-coded range..
我需要用最大值突出显示每列中的相应单元格，并尝试使用宏循环遍历它。但我只知道如何针对给定的硬编码范围进行操作。
Private Sub Worksheet_Activate()
Dim zelle As Range
For Each zelle In ActiveSheet.Range("B2:B21")
If zelle.Value = Application.WorksheetFunction.Max(Range("B2:B21")) Then
zelle.Interior.ColorIndex = 6
Else
zelle.Interior.ColorIndex = xlNone
End If
Next
End Sub
I tried to use a new range for column which I gave the Range ("B:IT") and iterate through that one but that didnt work.
我试图为列使用一个新的范围，我给了范围（“B：IT”）并遍历那个范围，但没有用。
Maybe it's just 2 or 3 lines?
也许它只是 2 或 3 行？
回答by Rachel Hettinger
This might work for you. Instead of using hard-coded ranges, it loops through whatever columns are used and adjusts for columns having different "lengths". It assumes a single header row and column.
这可能对你有用。它不使用硬编码范围，而是遍历使用的任何列，并针对具有不同“长度”的列进行调整。它假设有一个标题行和列。
Private Sub Worksheet_Activate()
Dim zelle As Range
Dim rng As Range
Dim lCol As Long
Dim lLastRow As Long
With ActiveSheet
For lCol = 2 To .UsedRange.Columns.Count
lLastRow = .Cells(.Rows.Count, lCol).End(xlUp).Row
Set rng = .Range(.Cells(2, lCol), .Cells(lLastRow, lCol))
For Each zelle In rng
If zelle.Value = Application.WorksheetFunction.Max(rng) Then
zelle.Interior.ColorIndex = 6
Else
zelle.Interior.ColorIndex = xlNone
End If
Next
Next lCol
End With
End Sub
回答by Robert Ilbrink
An alternative way to do this is without VBA, is to
另一种方法是没有 VBA，是
- Calculate the maximum value e.g. at the bottom (=
MAX(A1:A10)) and - To use conditional formatting, highlighting the cell(s) that match the result of your =
MAX(A1:A10)calculations.
- 计算最大值，例如在底部 (=
MAX(A1:A10)) 和 - 要使用条件格式，请突出显示与 =
MAX(A1:A10)计算结果匹配的单元格。
I know that the question referred to VBA, but this makes it dynamic and VBA independent.
我知道这个问题提到了 VBA，但这使它动态且独立于 VBA。
回答by chance
Use variables:
使用变量：
Range(Cells(row_var_1, col_var_1),Cells(row_var_2, col_var_2))
Where row_var_1, col_var_1, row_var_2and col_var_2are variables that may be iterated in your loop.
其中row_var_1，col_var_1，row_var_2并且col_var_2是可以在你的循环迭代变量。
