Conditional Formatting Color Scale More Than One Rule On Same Cells OR 4-Color Scale

Hello, this is the first time I'm using this forum but I am desperate. I use conditional formatting for tracking many things and I like to use gradients to visually represent information. But the only options for color scales are 2 and 3 color options. I have a situation where I have 4 different categories and so I need to have 4 different colors going across a gradient.

Example: 0 = red, 10 = orange, 20 = yellow, 30 = green, etc with gradients going across them

I have a 3-color scale on the cells, so I use a 2-color scale to make the last bit that is needed, but then one of them overrides the other. Even if I have 0-19 for one rule and 20-30 for another rule, if the 0-19 is listed first then the 20-30 rule just doesn't show up at all.

Is there ANY way that I can have a gradient that goes from red to orange to yellow to green?

This isn't a gradient, but rather 4 different solid colors. The reason I need a gradient is because, for example, if I am tracking something and had 90-100 = green and 80-89 = yellow, the difference between an 89 and a 90 really isn't much and yet it has a whole different color, but 80 is much different than 89 and it has the same color.

But thank you for answering, I take it that there is not actually any way to do what I want to here?

What you want cannot be done with built-in features.

This is a VBA solution. However, it creates permanent formatting, not conditional formatting. In this example, click the button to apply formatting to E2:E42. Change the code to match the range you actually want to color. Use column G and H starting in row 2 to put the colors you want to use and the starting value to use that color. The code then interpolates the colors between those points. The last starting number will result in any number larger than that getting that color (i.e., in your example, 31, 52, and 12,043 will all be green). You can use any number of colors but use strictly ascending values or you will get weird results.