CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-2016
fejk
fejk

Reputation: 25

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?

This is what I currently have:

Example 1

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:

Example 2

Upvotes: 2

Views: 2657

Answers (1)

BigBen
BigBen

Reputation: 50008

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

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

Before

enter image description here

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

enter image description here

Upvotes: 2

Related Questions