Heat Map on Multiple Rows

[mst3kr]

Hello all. I have a large list of items (anywhere from 1,500 to 2,500) that I want to apply a heat map to but am running into some challenges.

My spreadsheet has weeks across the top and items down the left with sales information for each item/week at their corresponding intersecting cells. I want to do a heat map for a specific row based on that row's sales information. When I apply the conditional formatting/color scales for a row, the heat map works fine. However, if I try to copy the conditional formatting down to the remaining rows at once, the heat map will looking at the rest of the rows collectively instead of treating each row as its own.

Is there a way to do a mass conditional formatting that treats each row independently?

 

[MarcelBeug]

If you want to have a heatmap for each row, then you'll end up with a lot of rules (1,500 to 2,500).
It could be done with VBA, like the code below that will copy the formats (including conditional formatting) from A1:K1 (suppose this is your first data row) to lines 2 through 1750, thus creating a rule for each line.

Sub fillCF()
    Dim i%
    Range("A1:K1").Copy
    For i = 2 To 1750
        Range("A" & i).PasteSpecial xlPasteFormats
    Next i
End Sub

My suggestion would be to define about 10 colors or so varying from red to green, each with a corresponding conditional formatting rule.
These rules based on the row values relative to the minimum and maximum value per row.

For instance suppose you have values in columns A:K, then create helper fields in column L and K with the minimum and maximum values, e.g. in L1 =MIN(A1:K1).
Now create a rule for red with formula: =((A1-$L1)/($M1-$L1))<0.05
And a rule for a bit more orange with formula: =((A1-$L1)/($M1-$L1))<0.15
And so on, each rule with option "Stop if True" checked.

 

[MarcelBeug]

Correction: helper fields in column L and M (not K).

 

[mst3kr]

Thanks Marcel! Your VBA code worked perfectly! I added in code to turn calculations to manual before it ran and then to automatic after it completed all the rows to help the copy/paste go a little faster. I thought about using VBA but disregarded it thinking it would end up being too complex. Nice to see I was wrong! I will give the other conditional formatting option you described a shot, too. I think that could have applications when the data isn't as large as what I'm dealing with now and still give me the option to customize my heat map...