Conditional formatting cell color based on a number range.

[Rixn]

In conditional formatting I can only get multiple cells to be colored with a range of colors from a gradient from one color to another (or via a third color).

I want to set the background color of one cell, based on the number that is entered, and the range in numbers should pick the color value based on the same kind of a gradient one can have in conditional formatting.

Example: If the number range is 1-5 and the color range goes from red to yellow to blue, then the fallout of the numbers should be the following:

1: red
2: orange
3: yellow
4: green
5: blue

It should work also if I change the number range (like 1 to 100).
Preffrable without VBA.

 

[Cubist]

Have you looked at Conditional Formatting -> Color Scales?

 

[awoohaw]

Here is the color scales dialog entry that @Cubist was suggesting:
this only uses three colors though. So it doesn't have the green you asked for.

 

[Rixn]

Here is the color scales dialog entry that @Cubist was suggesting:
this only uses three colors though. So it doesn't have the green you asked for.
View attachment 108366

Yes, I have tried it. But it is limiting as it only shows the different colors when you have multiple cells.
I want only one cell that get the color of these numbers (shown by awoohaw).
So when that cell have number 50 it will be yellow, and for 75 i will be green.
That is, the color value between the chosen colors for Midpoint and Maximum.

 

[awoohaw]

I am confused by your question then. In the example above, if you change the value in any of those cells the color will also change. The example did use the sequence function, but you can paste that as values and then change any of the numbers as you like.

Mr Excel Questions 75.xlsm
	A	B	C	D	E	F	G	H	I	J
1	1	2	3	4	5	6	7	8	9	10
2	11	12	13	14	15	16	17	18	19	20
3	21	22	23	24	25	26	27	28	29	30
4	31	32	33	34	35	36	37	38	39	40
5	41	42	43	44	45	46	47	48	49	50
6	51	52	53	54	55	56	57	58	59	60
7	61	62	63	64	65	66	67	68	69	70
8	71	72	73	74	75	76	77	78	79	80
9	81	82	83	84	85	86	87	88	89	90
10	91	92	93	94	95	96	97	98	99	100
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:J10	Other Type	Color scale		NO