对于每个列突出显示最大值（Excel）

时间：2021-04-08 13:08:46

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.

我尝试使用一个新的列范围,我给了Range(“B:IT”)并迭代了那个但是没有用。

Maybe it's just 2 or 3 lines?

也许只有2或3行?

3 个解决方案

#1

6  

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

#2

2  

An alternative way to do this is without VBA, is to

另一种方法是在没有VBA的情况下执行此操作

  1. Calculate the maximum value e.g. at the bottom (=MAX(A1:A10)) and

    2. 计算最大值,例如在底部(= MAX(A1:A10))和

  2. To use conditional formatting, highlighting the cell(s) that match the result of your =MAX(A1:A10) calculations.

    3. 要使用条件格式,请突出显示与= MAX(A1:A10)计算结果匹配的单元格。

I know that the question referred to VBA, but this makes it dynamic and VBA independent.

我知道这个问题提到了VBA,但这使它变得动态且与VBA无关。

对于每个列突出显示最大值（Excel）

#3

0  

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_2 and col_var_2 are variables that may be iterated in your loop.

其中row_var_1,col_var_1,row_var_2和col_var_2是可以在循环中迭代的变量。

#1

6  

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

#2

2  

An alternative way to do this is without VBA, is to

另一种方法是在没有VBA的情况下执行此操作

  1. Calculate the maximum value e.g. at the bottom (=MAX(A1:A10)) and

    2. 计算最大值,例如在底部(= MAX(A1:A10))和

  2. To use conditional formatting, highlighting the cell(s) that match the result of your =MAX(A1:A10) calculations.

    3. 要使用条件格式,请突出显示与= MAX(A1:A10)计算结果匹配的单元格。

I know that the question referred to VBA, but this makes it dynamic and VBA independent.

我知道这个问题提到了VBA,但这使它变得动态且与VBA无关。

对于每个列突出显示最大值（Excel）

#3

0  

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_2 and col_var_2 are variables that may be iterated in your loop.

其中row_var_1,col_var_1,row_var_2和col_var_2是可以在循环中迭代的变量。

标签：

相关文章