VBA Graded Color Scale - changes based on value in another cell

[RobRiotUK]

Hi there,

I'm new to the forum. So please be gentle

I'm trying to come up with a solution to have a column of numbers appear in a graded color sequence (similar to what the color scales in conditional formatting do), but have the high and low numbers used for the grading changed based on the value of another cell.

In column D I have 5 different values: "North", "South", "East", "West", "International". There is then a series of numbers in column L I want the color scaling to be applied to.

So say the highest value in column L for "North" was 25, and the lowest was 1 - 25 would show as red and 1 green, with all values in between for "North" as a gradient between those two colors. "South" could have different values in column L though. So for "South" 267 could be the highest and 24 the lowest. In this instance I want 267 to show as red and 24 as green, with all values in between for "South" as a gradient between those two colors.

I hope that makes sense?

The high and low numbers for each column D value will change. So I suppose I need a way for the VBA to also determine what is the lowest and highest numerical value in column L to help determine the color scaling too.

Thanks in advance for any help you can provide.

 

[RobRiotUK]

Can anyone provide any guidance please? Thanks in advance.

 

[RobRiotUK]

I know I'm new to the forum. But was hoping someone might be able to shed some light on this for me.