Conditional formatting specifying 5 colours

[magnum55]

Hi,
I’m trying to create conditional formatting using a 5 colour range where I can specify the 5 colours (I know excel does a 3 colour using the wizard).
However, I’d like each of the conditional formatting based on numerical values to be specific to each row.
Ie row 2 will have number ranges between 1-5 but could change to be 1-10 next month
Row 3 will have ranges between 50-100 but could change to be 50-1000 next month. Is there a way to do this as each row will potentially change value every month

So colours would be black, dark blue, green, yellow and orange.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, it might help if we had 5 or 6 rows of varied sample data, manually coloured and explain again in relation to the manually coloured samples.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[magnum55]

Hi, I've had to change the problem description as I realised there were some errors. Basically I want to create new Conditional Formatting rules so as a start I've used the 3 Color Scales. Ive picked one end of the range to be Orange and the other Blue with the mid point of yellow. The problem is its giving me some grey cells (see Car and Bike, April) rather than a lighter blue. Is there a way to change the transition colours (see attached image) so i can specify say 5 colours along the range rather than 3? I'm not sure if this complicates things but the data in each row have different ranges so each row is conditionally formatted (as opposed to the whole sheet.
I've attached the xls as well with real data.

Example 1.xlsx
	A	B	C	D	E	F
1		Jan	Feb	Mar	Apr	May
2	Van	-0.24	1.11	-1.15	1.11	-1.96
3	Car	-0.79	1.03	-0.06	0.54	-0.29
4	Bike	0.5	-0.39	-0.16	0.12	-0.39
5	Lorry	0.36	0.03	-0.31	-0.19	0.46
6	Plane	3.64	-3.01	3.31	-11.46	7.87
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6:F6	Other Type	Color scale		NO
B5:F5	Other Type	Color scale		NO
B4:F4	Other Type	Color scale		NO
B3:F3	Other Type	Color scale		NO
B2:F2	Other Type	Color scale		NO

 

[Peter_SSs]

That helps a bit, but it is showing what you don't want.

It would still help to clarify, for me at least, if you showed what you did want, manually coloured and with explanation in relation to that, as I asked above.

 

[magnum55]

Hi, Apologies. Here you go. This is the type of conditionally formatting I'm looking for. Note Car and Bike, April which have manually been changed to shades of blue rather than grey in the original.

Example 1.xlsx
	A	B	C	D	E	F
1		Jan	Feb	Mar	Apr	May
2	Van	-0.24	1.11	-1.15	1.11	-1.96
3	Car	-0.79	1.03	-0.06	0.54	-0.29
4	Bike	0.5	-0.39	-0.16	0.12	-0.39
5	Lorry	0.36	0.03	-0.31	-0.19	0.46
6	Plane	3.64	-3.01	3.31	-11.46	7.87
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6:F6	Other Type	Color scale		NO
B5:F5	Other Type	Color scale		NO
B4:D4,F4	Other Type	Color scale		NO
B3:D3,F3	Other Type	Color scale		NO
B2:F2	Other Type	Color scale		NO

 

[Peter_SSs]

I'm still struggling to fully understand. You originally talked about 5 colours and specified those colours.
Your latest sample ..

• does not use those 5 colours in any row, and
• uses 13 different colours!

It would still help to clarify, for me at least, if you showed what you did want, manually coloured and with explanation in relation to that, as I asked above.

 

[magnum55]

Hi,
Hmm ok. Maybe my explanation isn’t very clear,
Excel allows you to pick 3 colours in the conditional formatting scale then thematically chooses a colour based on the values per row. That cell however isn’t limited to the 3 colours as they transition from one colour to another. In this case I’ve chosen orange to yellow to blue. However as the colours transition from blue to yellow, at some point they’ll show as grey based on the values in that row. I’m trying to find a way to not show a grey colour but to show a light blue instead. So I was hoping to instead of 3 colours, is there a way to choose five colours to force excel to colour a lighter blue. I was assuming being able to specify 5 colours may be the solution. Ie orange to light orange to yellow to light blue to blue.

 

[Peter_SSs]

I don't think that it will be possible with the built-in color-scale options (Happy to be proved wrong though).
However, another approach 'may' be possible, depending on exactly what you would require. That is what I have continually been trying to establish by way of a variety of examples.