Conditional Formatting Color Scales by Category

Hello,

I'm trying to apply conditional formatting to a range so that it creates color scales, separately for each value in another column. For example.

Fruit Size
Apple 2
Apple 3
Apple 2
Apple 1
Grape 0.2
Grape 0.3
Melon 10
Melon 12
Melon 9

I want the 2nd, 6th, and 8th lines to be green, the 4th, 5th, and 9th lines to be red, and the rest to be in between.
I realize that I could separately set up conditional formatting for each range manually, but my dataset is large and will be changing, so I need it to be automatic.

Thank you,
K64

This isn't exactly conditional formatting. It's a method I had to come up with for a dashboard application that can't do color formatting. What I did was made a list of numbers from 0% to 100% in increments of 5 and then applied conditional formatting Red to Green on these values and got the RGB values of the colors. This gave me the table on the color tab. I threw in the graphical interpretation just for grins.

Since the color chart is based on percentiles, I used a helper cell to calculate the percentile for each fruit.
=PERCENTRANK(IF([Fruit]=[@Fruit],IF(NOT(ISBLANK([Size])),[Size],FALSE)),[@Size])
This is an array formula, so it is entered with CTRL-SHIFT-ENTER.
Sub ColorIt()
Dim cl As Range

For Each cl In Range("A2:A21")
cl.Interior.Color = RGB(cl.Offset(0, 3).Value, cl.Offset(0, 4).Value, cl.Offset(0, 5).Value)
Next

End Sub
[/code]

Then I used the percentile to look up the RGB values.

From there I had to apply a VBA subroutine to apply the colors
[code]