Conditional formatting

[Bartholomeu]

Apologies if this in the wrong sub-forum.

I have a table that has some names, associated with some store numbers and units. I am trying to create a table that just shows the names and the store numbers, with the store numbers for each name having conditional formatting based on rank (ranking least units to most).

I'm a bit lost as to how to achieve this, especially in an aesthetic way, like with a colour scale, as I don't think colour scales can format one set of data based on another in the way I want.
If I haven't been clear on what I'm trying to achieve, please let me know and I'll try to explain it better.

Book2.xlsx

[6StringJazzer]

You are correct. Conditional formatting, other than using a formula, can format only the cells containing the values of interest. And you can't do color scales with a formula.

One option I can think of is to use CF applied to the units, then use VBA to make the store numbers match the coloring applied on the units.

BUT you said "a table that just shows the names and the store numbers." If it doesn't include the units, then how can you rank them by units?

[Bartholomeu]

The units columns would be hidden.

[6StringJazzer]

Attached is a VBA solution. The colors are set for a store every time there is any change in the Units for that store. But I'm not sure how that would happen if they're hidden. I have a feeling that there are important details about your question that are left out.

What is the realistic number of stores can occur for each store name? This solution assumes the max is 97. The formula uses 99 so it sticks out.

Also I discourage the use of merged cells. It prevented me from copying formatting from one column to another, and can cause all kinds of other problems.