Excel多行条件色标 [英] Excel conditional colour scale for multiple rows

问题描述

我有一个电子表格，其中显示了一系列产品的库存数据.我每天都有一列，这些值显示了我的库存水平将如何随着时间下降.

I have a spreadsheet that shows stock data for a range of products. I have a column for each day and the values show how my stock levels will decrease over time.

我想使用色标来轻松查看某些产品的库存何时耗尽.我的秤的最小，中点和最大点是基于不同列中的值，并且每个产品的该值都不同.根据它是中点还是最大点，它会相乘.不幸的是，色标不支持相对引用，这意味着我不得不从第一行复制条件格式，并更改每隔一行的最小，中点和最大点引用.当我的工作表上有数百行时，是否可以解决此问题?

I want to use colour scale to make it easy to see when the stock of certain products will be running low. The minimum, mid and maximum points of my scale are based on a value in a different column and that value is different for each product. Depending on whether it's the midpoint or the maxpoint, it gets multiplied. Unfortunately, the colour scale does not support relative references and it means that I am having to copy the conditional formatting from the first row and change the min, mid and max points references for every other row. Is there a way around that as I have hundreds of rows on my sheet?

这是我目前拥有的:

当我尝试编辑第二行的条件格式时，我可以看到中点和最大点引用仍然来自上面的行，因为它们是绝对引用:

When I am trying to edit the conditional formatting for the second row, I can see that the midpoint and maximum point references are still from the row above, because they are absolute references:

推荐答案

每行都需要一个单独的规则，并且可以使用 vba .

You'll need a separate rule for each row, and you can automate creating these rules with vba.

下面的代码在公式=$D$3*3和=$D$3*5中调整行号.注释指出您可能需要更改工作表名称，行数和列字母的位置.

The code below adjusts the row number in the formulas =$D$3*3 and =$D$3*5. Comments point out where you might need to change the Sheet name, row count and column letters.

Option Explicit
Sub ApplyConditionalFormatting()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") ' change to your sheet here
    Dim rw As Long
    Dim rng As Range

    For rw = 3 To 8 ' change to your respective rows
        With ws
            Set rng = .Range(.Cells(rw, "E"), .Cells(rw, "K")) ' change to your respective columns

            With rng
                .FormatConditions.AddColorScale ColorScaleType:=3
                .FormatConditions(.FormatConditions.Count).SetFirstPriority  ' now its index is 1, in case there already was cond formatting applied
            End With

            With rng.FormatConditions(1)
                With .ColorScaleCriteria(1)
                    .Type = xlConditionValueNumber
                    .Value = 0
                    .FormatColor.Color = 7039480
                End With

                With .ColorScaleCriteria(2)
                    .Type = xlConditionValueFormula
                    .Value = "='" & ws.Name & "'!$D$" & rw & "*3" ' References column D, change as needed
                    .FormatColor.Color = 8711167
                End With

                With .ColorScaleCriteria(3)
                    .Type = xlConditionValueFormula
                    .Value = "='" & ws.Name & "'!$D$" & rw & "*5" ' References column D, change as needed
                    .FormatColor.Color = 8109667
                End With
            End With
        End With
    Next rw
End Sub

之前

之后-显示第8行的规则；请注意该公式是指$D$8

After - showing the rule for row 8; notice the formula refers to $D$8

这篇关于Excel多行条件色标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！