Conditional formatting; 3-colour colourshades as percentage of a cell

Hi there!

This is my first post on this forum. I'm Dutch. I have tried this question on a Dutch Excel support forum but to no avail. Hence, I'm trying my luck here.

Sheet1EN.xlsx

I have a column C with numbers in it. These are fixed values. In column E I will be entering numbers manually. I would like to compare the values in column C with those in column E. After entering a value in column E, I would like the cell to adopt a colour, depending on the percentage of the cell the same row in column C. The higher the precentage, the redder. The lower the percentage, the greener and yellow in the middle. It should be something in the line of the colourshades function in the conditional formatting.

So if I have a value of 80 in cell C3 and in cell E3 I enter a value of 1, then E3 should turn dark green (1,25%). When I enter a value of 40 (50% of 80), then E3 should turn yellow. When I enter a value of 80 or higher ((over) 100%), the cell should turn red.

Conditions:
1. When no value is entered into E3, the colour of the cell should remain unchanged (white).
2. Cells E3 until E6 should have a colour that relates to cells C3 until C6, in the sense that the colour represents a percentage of the corresponding value in the C column. The closer to 0% the greener, the closer to 100%, the redder and yellow at 50%.
3. What I failed to manage is to hide the value of F2 in case all cells of G3 until G6 are empty. When at least one of these cells contains a value, the value of F2 should be shown; in red if it is a negative number and dark green if it is a positive numer. Black if the number is zero.
4. A nice to have would be that the more 100% is exceeded, the darker red the cell becomes (maybe even approaching black, but then the value won't be visible anymore because is is also in black). In theory the percentage value may reach 500% although I think that the vast majority of the values will not exceed the 100% that much (if at all).

I made the worksheet in a Dutch version of Excel, but I tried to translate the formulas into English. I hope this works.

Your suggestions are highly appreciated! Thank you.

Hi Jazz,

I have made the changes. Hope this fulfills your need within the file.

Thanks for your very quick reply.

Unfortunately it is not exactly what I had in mind as the colours lack any disctinction in shades. It just green, yellow and red. But I would like to have 0% highlighted as dark green but 1% a bit less green and a bit more yellow; all the way up to 100% (and beyond). So the colours change gradually from everything between green, yellow and red and not just simply 3 colours.

Yes, I'm aware of that. However, I don't seem to get it to work. It seems to me that this function (the percentage) relates to the different values in the E column and not to those in the C column to which I want them to relate.